Validate the data and load into below three Interface tables and run standard import program.' Import Standard Purchase Orders program'
| Table Name | Description |
| PO_HEADERS_INTERFACE | This is the table where to insert PO headers data in interface table. |
| PO_LINES_INTERFACE | This is where we insert PO lines information to be imported ( it is used also for Shipments details ) |
| PO_DISTRIBUTIONS_INTERFACE | This is where we insert PO distribution details before import |
| PO_INTERFACE_ERRORS | Stores all errors resulted from import process. |
| PO_HEADERS_ALL | Stores document headers for purchase orders, purchase agreements,quotations, and RFQs |
| PO_LINE_LOCATIONS_ALL | Stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs |
| PO_DISTRIBUTIONS_ALL | Stores purchase order distributions |
INSERT INTO po_headers_interface
( interface_header_id
, org_id
, document_num
, document_type_code
, release_num
, revision_num
, currency_code
, effective_date
, approval_status
, approved_date
, action
, agent_id
, ship_to_location_id
, bill_to_location_id
, vendor_id
, vendor_site_id
, creation_date
, ATTRIBUTE1
, ATTRIBUTE2
)
VALUES
( po_headers_interface_s.NEXTVAL -- interface_header_id
, v_spphi_org_id -- org_id
, v_spphi_document_number -- document_num
, v_spphi_document_type_code -- document_type_code
, v_spphi_release_num -- release_num
, v_spphi_revision_num -- revision_num
, v_spphi_currency_code -- currency_code
, v_spphi_effective_date -- effective_date
, v_spphi_approval_status -- approval_status
, v_spphi_approved_date -- approved_date
, v_spphi_action -- action
, P_AgentId -- agent_id
, P_LocnId -- ship_to_location_id
, P_LocnId -- bill_to_location_id
, v_vendor_id -- vendor_id
, v_vendor_site_id -- vendor_site_id
, c_creation_date -- creation_date
, 'SETC1'
, 'SE'
);
--
-- INSERT ITEM line into PO_LINES_INTERFACE
--
INSERT INTO po_lines_interface
( interface_header_id
, interface_line_id
, action
, line_num
, shipment_num
, category
, item_description
, unit_of_measure
, quantity
, unit_price
, tax_name
, closed_code
, creation_date
, ship_to_organization_id
, ship_to_location_id
-- , line_location_id
, organization_id
)
VALUES
(
po_headers_interface_s.CURRVAL -- interface_header_id
, po_lines_interface_s.NEXTVAL -- interface_line_id
, v_sppli_action -- action
, v_sppli_line_number -- line_number
, v_sppli_shipment_num -- shipment_num
, NVL(v_sppli_category,'AA') -- categor)y
, v_sppli_item_description -- item_description
, c_unit_of_measure -- unit_of_measure
, v_sppli_quantity -- quantity
, v_sppli_unit_price -- unit_price
, UPPER(v_sppli_tax_name) -- tax_name
, v_sppli_closed_code -- closed_code
, c_creation_date -- creation_date
, v_spphi_org_id -- v_spphi_org_id
, P_LocnId -- Location ID
--, P_LocnId -- Location ID
, v_spphi_org_id
);
--
-- Insert item line into po_distributions_interface
--
INSERT INTO po_distributions_interface
( interface_header_id
, interface_line_id
, interface_distribution_id
, distribution_num
, quantity_ordered
, charge_account_id
, charge_account_segment1
, charge_account_segment2
, charge_account_segment3
, charge_account_segment4
, charge_account_segment5
, charge_account_segment6
, charge_account_segment7
, project
, task
, expenditure_type
, expenditure_organization
, attribute1
, attribute2
, project_accounting_context
, expenditure_item_date
, project_releated_flag
, creation_date
, org_id
, set_of_books_id
)
VALUES
( po_headers_interface_s.CURRVAL -- interface_header_id
, po_lines_interface_s.CURRVAL -- interface_line_id
, po_distributions_interface_s.NEXTVAL -- interface_distribution_id
, v_sppdi_distribution_num -- distribution_num
, v_sppli_quantity -- quantity_ordered
, v_sppdi_charge_account_id -- charge_account_id
, v_sppdi_charge_acct_segt1 -- charge_account_segment1
, v_sppdi_charge_acct_segt2 -- charge_account_segment2
, v_sppdi_charge_acct_segt3 -- charge_account_segment3
, v_sppdi_charge_acct_segt4 -- charge_account_segment4
, v_sppdi_charge_acct_segt5 -- charge_account_segment5
, v_sppdi_charge_acct_segt6 -- charge_account_segment6
, v_sppdi_charge_acct_segt7 -- charge_account_segment7
, v_sppdi_project -- project
, v_sppdi_task -- task
, v_sppdi_expenditure_type -- expenditure_type
, v_sppdi_expenditure_org -- expenditure_organization
, v_spphi_document_number -- po_num
, v_sppli_line_number -- line_num
, v_sppdi_attribute3 -- project_context
, c_expenditure_item_date
, v_sppdi_attribute3
, c_creation_date
, P_orgId
, P_SetOfBooksId
);
No comments:
Post a Comment