Procure to Pay workflow with backend effected Tables.

Modules involved in Procure to Pay Process.
1.Inventory
2.Purchasing
3.Payables
4.General Ledger

CREATE AN ITEM
===============
SELECT * FROM MTL_SYSTEM_ITEMS WHERE SEGMENT1 LIKE 'P8P_ITEM'

THEN SAVE IT.

THE NEXT WE NEED TO RUN THE MIN MAX PLANNING REPORT INORDER TO SEND
===================================================================== THE DATA TO REQUSITION INTERFACE TABLES.
======================================

THEN THE EFFECTED TABLES ARE:
============================
SELECT * FROM PO_REQUISITIONS_INTERFACE_ALL WHERE ITEM_ID='249210'


THEN NEXT GOTO PURCHASING MODULE AND RUN THE REQUISITION IMPORT PROGRAM
=======================================================================

NOW THE DATA COMES INTO STANDARD REQUISITION BASE TABLES

SELECT * FROM PO_REQUISITIONS_INTERFACE_ALL WHERE ITEM_ID='249208'

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE REQUISITION_HEADER_ID='551801'
SELECT * FROM PO_REQUISITION_LINES_ALL  WHERE ITEM_ID='249210'
SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL-- THIS TABLE AT THIS STAGE DO NOT HAVE ANY DATA IN IT BECAUSE AT THIS TIME ONLY REQUISTIONS ARE CREATED.

NOW WE NEED TO CREATE THE REQUSET FOR QUOTATIONS FOR THE ABOVE REQUISTIONS CREATED.

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1 LIKE '315%'
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=318879
SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_LINE_ID='391234'--THIS TABLE DON'T HAVE ANY DATA AT THIS TIME, BECAUSE WE ARE JUST CREATING RFQ'S AT THIS MOMENT.


NOW RUN THE PRINTED RFQ REPORT BY ADDING THE SUPPLIER LIST TO THE RFQ IN ORDER TO SEND THE THE REPORT TO THE SUPPLIERS IN THE FORM OF FAX OR MAIL
=================================================================================================================================================

THEN THE EFFECTED TABLES ARE:
=============================
SELECT * FROM PO_RFQ_VENDORS WHERE PO_HEADER_ID=318879

NEXT AS A SUPPLIER WE CREATE THE QUAOTATIONS AND SEND TO THE CUSTOMERS
========================================================================

NOW THE EFFECTED TABLES ARE:
============================

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1 IN ('504','505','506') AND TYPE_LOOKUP_CODE='QUOTATION'--WE ARE GETTING THE QUOATAIONS FROM THE SUPPLIERS
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID IN ('318880','318881','318882')
SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_LINE_ID IN('391235','391236','391237')--AT THIS STAGE WE DONT HAVE ANY DATA IN THIS TABLE.

NOW AS A CUSTOMER WE NEED TO DO THE QUOATE ANALYSIS AND APPROVE THE QUOTATIONS:
===============================================================================

NOW THE QUOTATIONS ARE APPROVED AND THROUGH QUAOTE ANALYSIS

NOW WE NEED TO CREATE THE PURCHASE ORDERS FOR THE ABOVE QUOTATION THAT ARE APPROVED
===================================================================================





SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1 LIKE '6643'
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=318883
SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_LINE_ID=391238
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=391238

AS WE HAVE PROBLEM IN THE SETUP PART WE CANNOT APPROVE THE PO, FOR NOW 
========================================================================
LETS CREATE A INVOICE WITH SOME RANDOM PO NUMBER OR WITHOUT A PO NUMBER.
======================================================================
NOW WE HAVE TO SHIFT TO PAYABLES MODULE AND CREATE THE INVOICES FOR THE ABOVE PURCHASE ORDERS
=============================================================================================

EFFECTED BACKEND TABLES ARE:
===============================
SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM LIKE '%OCT%17'
SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=580059
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=580059--THIS TABLE HAS THE ACCOUNTING EVENT ID

NOW WE NEED SELECT THE PAY IN FULL OPTION AND CREATE ACCOUNTING FOR THE ABOVE INVOICE.
=======================================================================

EFFECTED BACKEND TABLES:
===========================
SELECT * FROM XLA_EVENTS WHERE EVENT_ID=6231565--THIS TABLE HAS A COLUMN PROCESS_STATUS_CODE HAS 'P' THAT MEANS THE THEY ARE INTERFACED TO GL INTERFACE TABLES AND REFERENCE 26 HOLDS THE ACCOUNTING EVNET_ID
SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID=6231565
SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=8116566


THEN WHEN WE SUBMIT FOR ACCOUNTING THE FOLLWING JOBS KICKS OFF

NOW WE NEED TO GO FOR THE INTERFACE TABLES
===========================================

SELECT * FROM GL_INTERFACE WHERE REFERENCE26='6231565'
SELECT * FROM GL_JE_BATCHES WHERE NAME LIKE 'Payables A 3830422 7540244'--IN THIS TABLE WE HAVE STSTUS AS 'P' WHICH REFERS TO POSTED
SELECT * FROM GL_JE_HEADERS WHERE JE_BATCH_ID=5363732
SELECT * FROM GL_JE_LINES WHERE JE_HEADER_ID=7117875

NOW WE HAVE TO POST THE BALANCES TO GL
======================================

NOW SHIFT THE RESPONSIBILITY TO  GENERAL LEDGER, IFRS REPORTING VISION OPS
THEN OPEN THE PERIODS IF THEY ARE NOT OPEN BY GOING TO OPEN/CLOSE PERIODS AND THEN POST THE JOURNALS
====================================================================================================

SELECT * FROM GL_BALANCES WHERE CODE_COMBINATION_ID=12975 AND  PERIOD_NAME LIKE '%Oct%17%'

NOW WE NEED TO MAKE THE PAYMENTS FOR THE ABOVE INVOICES
===========================================================

NOW GO TO PAYMENTS FORM IN PAYABLES MODULE AND CREATE PAYMENTS FOR THE RESPECTIVE ABOVE INVOICE NUMBER OR ELSE IN THE INVOICE ITSELF WE CAN CREATE THE PAYMENTS BY SELECTING THE PAY IN FULL OPTION AND FOLLOW THE STEPS THEN AFTER POST ALL THE INVOICE TO GL COME BACK TO THE PAYMENTS TABS AND CREATE THE ACCOUNTING FOR THE PAYMENTS THAT ARE DONE.

NOW ONCE THE PAYMENTS ARE PROCESSED THEN FOLLOWING TABLES ARE 
=================================================================
EFFECTED IN THE BACKEND:
========================
SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=580059--THIS TABLE THE PAYMENT DONE TO THE PARTICULAR INVOICE WHERE ACCURAL POSTED FLAG AND POSTED FLAG='Y'

SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID=580059--THIS TABLE TELLS THE INFORMATION ABOUT THE REMAINING BALANCE NEED TO BE PAID

SELECT * FROM AP_CHECKS_ALL WHERE CHECK_ID=162704--WE GET THE CHECK ID FROM AP_INVOICE_PAYMENTS_ALL

SELECT * FROM IBY_PAYMENTS_ALL WHERE PAYMENT_ID=105837--IBY_PAYMENTS_ALL STORES INFORMATION ON PAYMENTS. A PAYMENT IS A SINGLE CHECK OR FUND TRANSFER TRANSACTION BETWEEN FIRST PARTY PAYER AND THIRD PARTY PAYEE

SELECT * FROM IBY_DOCS_PAYABLE_ALL WHERE PAYMENT_ID=105837--IBY_DOCS_PAYABLE_ALL STORES ORACLE PAYMENTS' REPRESENTATION OF SOURCE PRODUCTS' DOCUMENTS PAYABLE. A DOCUMENT PAYABLE IS A SUPPLIER INVOICE OR SIMILAR DOCUMENT THAT NEEDS TO BE PAID. THIS TABLE CONTAINS THE IDENTIFIER FOR THE SOURCE PRODUCT'S DOCUMENT PAYABLE. IN ADDITION, THIS TABLE CONTAINS WHATEVER DOCUMENT INFORMATION IS NECESSARY FOR PAYMENT PROCESSING.

SELECT * FROM IBY_PAY_SERVICE_REQUESTS WHERE PAYMENT_SERVICE_REQUEST_ID=31464-- WE GET THE PAYMENT_SERVICE_REQUEST_ID FROM IBY_PAY_SERVICE_REQUESTS STORES PAYMENT PROCESS REQUESTS FROM SOURCE PRODUCTS. A PAYMENT PROCESS REQUEST IS A GROUP OF DOCUMENTS PAYABLE THAT A SOURCE PRODUCT SUBMITS TO ORACLE PAYMENTS FOR PAYMENT SERVICE PROCESSING. THIS TABLE CORRESPONDS TO THE PAYMENT PROCESS REQUESTS PAGE.

SELECT * FROM CE_BANK_ACCOUNTS

NOW WE NEED TO CREATE ACCOUNTING FOR THE PAYMENTS THAT ARE DONE ABOVE.






SUB LEDGER ACCOUNTING
=====================
SELECT * FROM XLA_EVENTS WHERE EVENT_ID=6231566
SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID=6231566
SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=8116567

NOW WE NEEED TO GO FOR THE INTERFACE TABLES
===========================================
SELECT * FROM GL_INTERFACE WHERE REFERENCE26=6231566
SELECT * FROM GL_JE_BATCHES WHERE NAME LIKE 'Payables A 3830423 7540253'
SELECT * FROM GL_JE_HEADERS WHERE JE_BATCH_ID=5363735
SELECT * FROM GL_JE_LINES WHERE JE_HEADER_ID=7117878



NOW SHIFT THE RESPONSIBILITY TO  GENERAL LEDGER, IFRS REPORTING VISION OPS
THEN OPEN THE PERIODS IF THEY ARE NOT OPEN BY GOING TO OPEN/CLOSE PERIODS AND THEN POST THE JOURNALS
====================================================================================================





SELECT * FROM GL_BALANCES WHERE PERIOD_NAME LIKE '%Oct%17%' AND CODE_COMBINATION_ID=12854 AND LEDGER_ID=1



******************************END of P2P CYCLE*********************************