Sunday, June 24, 2012

Create Requisitions Using Requisition Import - Training


Steps required importing a requisition in purchasing:
Use the following scripts to insert data into the po_requisitions_interface_all table. After inserting into the interface table, run the “Requisition Import” program.


Destination Type Inventory (Item AS54888).

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           ORG_ID,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           PREPARER_ID,
                                           CHARGE_ACCOUNT_ID,
                                           SOURCE_TYPE_CODE,
                                           UNIT_OF_MEASURE,
                                           LINE_TYPE_ID,
                                           QUANTITY,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           ITEM_ID,
                                           NEED_BY_DATE)
     VALUES ('IMPORT_INV',                                  --Interface Source
             204,                                             --Operating Unit
             'INVENTORY',                                   --Destination Type
             'INCOMPLETE',                                            --Status
             29,                      --This comes from per_people_f.person_id
             13185,           --Code Combination ID from V1 Inv Org Parameters
             'VENDOR',                                           --Source Type
             'Each',                                                     --UOM
             1,                                           --Line Type of Goods
             25,                                                    --quantity
             204,                      --Represents Vision Operations Inv Org.
             204,                                --Represents V1-New York City
             29,                            --This is the Deliver to Requestor
             149,                                                --Item AS5488
             SYSDATE + 2                                        --Need By Date
                        );

Destination Type Expense (One Time Item)

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           ORG_ID,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           PREPARER_ID,
                                           CHARGE_ACCOUNT_ID,
                                           SOURCE_TYPE_CODE,
                                           UNIT_OF_MEASURE,
                                           LINE_TYPE_ID,
                                           CATEGORY_ID,
                                           UNIT_PRICE,
                                           QUANTITY,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           ITEM_DESCRIPTION)
     VALUES ('IMPORT_EXP',                                  --Interface Source
             204,                                             --Operating Unit
             'EXPENSE',                                     --Destination Type
             'INCOMPLETE',                                            --Status
             29,                      --This comes from per_people_f.person_id
             13185,                                      --Code Combination ID
             'VENDOR',                                           --Source Type
             'Each',                                                     --UOM
             1,                                           --Line Type of Goods
             1,                                           --MISC.MISC Category
             12,                                                       --Price
             25,                                                    --quantity
             204,                      --Represents Vision Operations Inv Org.
             204,                                --Represents V1-New York City
             29,                            --This is the Deliver to Requestor
             'Expense Item'                        --One Time Item Description
                           );

Destination Type Expense - Multi-Distribution

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           batch_id,
                                           transaction_id,
                                           ORG_ID,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           PREPARER_ID,
                                           SOURCE_TYPE_CODE,
                                           UNIT_OF_MEASURE,
                                           LINE_TYPE_ID,
                                           UNIT_PRICE,
                                           QUANTITY,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           multi_distributions,
                                           req_dist_sequence_id,
                                           category_id,
                                           item_description)
     VALUES ('IMPORT_MULTI',                                --Interface Source
             98,                                                   -- batch_id
             99,                                             -- transaction_id
             204,                                             --Operating Unit
             'EXPENSE',                                     --Destination Type
             'INCOMPLETE',                                            --Status
             29,                      --This comes from per_people_f.person_id
             'VENDOR',                                           --Source Type
             'Each',                                                     --UOM
             1,                                           --Line Type of Goods
             10,                                                       --Price
             10,                                                    --quantity
             204,                                            --destination_org
             204,                       --Represents V1-New York City location
             29,                            --This is the Deliver to Requestor
             'Y',
             97,
             1,                                           --MISC.MISC category
             'Testing Multi');
            
INSERT INTO po_req_dist_interface_all (interface_source_code,
                                       batch_id,
                                       transaction_id,
                                       charge_account_id,
                                       distribution_number,
                                       dist_sequence_id,
                                       quantity)
     VALUES ('IMPORT_MULTI',
             98,
             99,
             13185,
             1,
             97,
             5);



Importing an Internal Requisition (Item CM96713)

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           ORG_ID,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           PREPARER_ID,
                                           CHARGE_ACCOUNT_ID,
                                           SOURCE_TYPE_CODE,
                                           SOURCE_ORGANIZATION_ID,
                                           UNIT_OF_MEASURE,
                                           LINE_TYPE_ID,
                                           QUANTITY,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           ITEM_ID,
                                           NEED_BY_DATE)
     VALUES ('IMPORT_INT',                                  --Interface Source
             204,                                             --Operating Unit
             'INVENTORY',                                   --Destination Type
             'INCOMPLETE',                                            --Status
             29,                      --This comes from per_people_f.person_id
             13401,           --Code Combination ID from M1 Inv Org Parameters
             'INVENTORY',                                        --Source Type
             208,
             'Each',                                                     --UOM
             1,                                           --Line Type of Goods
             25,                                                    --quantity
             207,                                     --Represents M1 Seattle.
             207,                                      --Represents M1-Seattle
             29,                            --This is the Deliver to Requestor
             484,                                               --Item CM96713
             SYSDATE + 2                                        --Need By Date
                        );