P2P CYCLE:
==========
ITEM CREATION:
==============
GO TO INV VISION OPERATIONS USA.
NAVIGATION:
==========
ITEMS->MASTER_ITEMS->IT WILL SHOW ORGANIZATION LIST ->SELECE V1.
SELECT * FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE='V1'--ORGANIZATION_ID IS PK.
ITEMS TABLE:
------------
SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='SIM_CUTTER'
THIS TABLE HAS COMPOSOITE PK ON (INVENTORY_ITEM_ID AND ORGANIZATION_ID).
ITEM NAME STORES IN SEGMENT1 COLUMN.
THIS ITEM WE CAN ALSO ATTACH TO DIFFERENT CATEGORIES.
TO KNOW CATEGORIES.
SELECT * FROM MTL_CATEGORIES--CATEGORY_ID IS PK.
SUPPLIER CREATION:
==================
GO TO PURCHASING,VISION OPERATIONS(USA)
NAVIGATION:
===========
SUPPLY BASE->SUPPLIERS->CREATE SUPPLIER -> ENTER THE DETAILS ->APPLY. ->IT WILL GENERATE SUPPLIER_NUMBER AND REGISTRY_ID.
COPY SUPPLIER_NUMBER AND PASTE IT IN SEGMENT1 COLUMN.
SELECT * FROM AP_SUPPLIERS WHERE SEGMENT1='13' --VENDOR_ID IS PK.39169.
GO TO ADDRESS BOOK AT THE LEFT SIDE.
NAVIGATION:
===========
ADDRESS BOOK -> CREATE -> ENTER THE DETAILS ->check all 3 check boxes(purchasing,payment,rfq only)
->continue ->select check box under select site name -> APPLY.
->CREATE(TO CREATE ANOTHER SUPPLIER SITE.)
TO KNOW THE SITES FOR THIS SUPPLIER.
SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_ID=39169 -- VENDOR_SITE_ID IS PK.
GO TO CONTACT DIRECTORY AT THE LEFT SIDE.
-> CONTACT DIRECTORY -> CREATE ->ENTER THE DETAILS.->APPLY.
SELECT * FROM AP_SUPPLIER_CONTACTS--VENDOR_CONTACT_ID IS PK.
TO DISPLAY CONTACT NAME
SELECT * FROM HZ_PARTIES WHERE PERSON_FIRST_NAME='KADURU'
BUYER CREATION:
===============
GO TO HRMS Management. RESPONSABILITY.
NAVIGATION:
===========
HRMS MANAGER ->PEOPLE -> ENTER AND MAINTAIN ->NEW(BUTTON) ->ENTER THE DETAILS ->
ACTION:CREATE EMPLOYEMENT ->Person Type For Action:EMPLOYEE ->CTRL+S. ->COPY EMPLOYEE NUMBER.
SELECT * FROM PER_ALL_PEOPLE_F WHERE PERSON_ID='31518'--PERSON_ID IS PK.
SELECT * FROM FND_USER WHERE USER_NAME='SITARAMA'--USER_ID IS PK.
TO ADD PERSON TO USER:
=======================
GO TO SYSTEM ADMINNISTRATOR RESP
SECURITY->USER->DEFINE->F11->USERNAME:SITARAMA ->CTRL+F11. ->CTRL+L ON PERSON FIELD. ->SELECT 'MUDALIAR%' ->CTRL+S.
TO MAKE USER AS A BUYER:
=========================
GO TO PURCHASIING,VISION OPERATIONS (USA).
NAVIGATION:
SETUP ->Personal ->BUYERS ->ADD BUYER(BUTTON) ->SELECT BUYERNAME AND SAVE THE DETAILS.
NOTE:
=====
BUYER IS NOTHING BUT AGENT.
SELECT * FROM PO_AGENTS WHERE AGENT_ID='31518' --AGENT_ID IS PK.
SELECT * FROM FND_USER WHERE EMPLOYEE_ID='31518'
SELECT * FROM PER_ALL_PEOPLE_F WHERE PERSON_ID='31518'.
For Items:
===========
select *from mtl_system_items_b where segment1='Nokia Mobile' and organization_id=204
select *from mtl_onhand_quantities where inventory_item_id=214955 and organization_id=204
select *from mtl_material_transactions where inventory_item_id=214955 and organization_id=204
select *from mtl_transaction_types where TRANSACTION_TYPE_ID=42
select *from mtl_item_categories where inventory_item_id=214955 and organization_id=204
select *from mtl_categories where CATEGORY_ID=1
select*from mtl_category_sets where CATEGORY_SET_ID=2 --to know category set name.
Employee And Buyer(agent) Informaation:
========= ============ ==========
select *from per_all_people_f where employee_number='2114' ----Person_id is Primary key
select *from po_agents where agent_id=31478
Suppier Informtion:
====== =============
select *from ap_suppliers where segment1=12
select *from hz_parties where party_name='Nokia Supplier'
select *from ap_supplier_sites_all where vendor_id=38169
select *from ap_supplier_contacts --where vendor_site_id=6971
select *from hz_party_sites where party_id=406680
select *from hz_party_relationships --where PARTY_RELATIONSHIP_ID=406683
select *from hz_contact_points
07-OCT-2014:
PO:
====
REQUISITION:
============
1.INTERNAL REQUISITION.
2.PURCHASE REQUISITION.
INTERNAL REQUISITION CALLED AS INTERNAL SALES ORDER ITS CONNECTED TO THE ORDER MANAGEMENT.
GENERALLY WE CREATE INTERNAL REQUISITION WHEN EVER GOODS RECEIVING FROM ANOTHER INVENTORY IN THE SAME ORGANIZATION.
WE CREATE THE PURCHASE REQUISITION WHEN EVER THE GOODS ARE RECEIVING FROM THE SUPPLIER.
TO CREATE THE REQUISITION GO TO THE PURCHASE,VISION OPERATIONS (USA):
=====================================================================
NAVIGATION:
===========
REQUISITIONS ->REQUISITIONS ->SELECT TYPE:PURCHASE REQUISITION.AND OPERATING UNIT:VISION OPERATIONS
AND ENTER THE DETAILS IN HEADER AND LINES LEVEL AND SELECT DISTRIBUTIONS BUTTON PLACE THE CURSOR IN CHARGE ACCOUNT
AND SAVE THE DETAILS.
AND CLOSE THE DISTRIBUTIONS TAB, COPY THE REQUISITION NUMBER.
SELECT APPROVE BUTTON. SELECT NEED FOR APPROVAL AND APPROVE IT.
TO SEE THE STATUS OF THE REQUISITION:
======================================
SELECT THE REQUISITION SUMMARY ->PASTE THE REQUISITION NUMBER AND ITEM ->SELECT FIND(BUTTON).
NOW U CAN SEE THE APPROVAL_STATUS.
TO SEE THE HISTORY OF THE REQUISITION ->SELECT TOOLS MENU ->VIEW ACTION HISTORY.
TO CANCEL THE REQUISITION ->SELECT TOOLS MENU ->CONTROL ->SELECT CANCEL REQUISITION ->
REASON:WE DONT WANT ->SELECT OK(BUTTON).
AND RE_QUERY TO SEE THE CHANGED REQUISITION STATUS.
FROM BACK END WE CAN SEE THE REQUISITION STATUS IN THE PO_REQUISITION_HEADERS_ALL
SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='14316' --REQUISITION_HEADER_ID IS PK.
IN SEGMENT1 COLUMN REQUISITION_NUMBER WILL STORE.
REQUISITION TABLES:
===================
HEADERS TABLE:
-------------
SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='14317'--REQUISITION_HEADER_ID IS PK.
REQUISITION_HEADER_ID=181311
LINES TABLE:
-------------
SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID='181311'--REQUISTION_LINE_ID IS PK.
REQUISTION_LINE_ID=208499.
DISTRIBUTIONS TABLE:
-------------------
SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID='208499'--DISTRIBUTION_ID IS PK.
DISTRIBUTION_ID=207018.
TO SEE THE PO_ACTION_HISTORY:
--------------------------------
SELECT * FROM PO_ACTION_HISTORY WHERE OBJECT_ID='181311'--OBJECT_ID IS NOTHING BUT REQUISITION_HEADER_ID.
AND OBJECT_TYPE_CODE='REQUISITION'
AND OBJECT_SUB_TYPE_CODE='PURCHASE'
IN THIS TABLE WE CAN SEE THE DETAILS OF REQUISITION WHO IS SUBMITTED AND WHO IS APPROVED
BY USING THE CREATED_BY COLUMN.
AND WE CAN FIND OUT WHICH RECORD BELONGS TO SUBMIT AND WHICH RECORD BELONGS TO APPROVE BY USING
THE "ACTION_CODE" COLUMN.
TO CHANGE REQUISITION NUMBER AUTOMATIC FROM MANUAL:
===================================================
BY DEFAULT REQUISITION NUMBER IS MANUAL.
GO TO PURCHASING RESP.
->SETUP -> ORGANIZATION -> PURCHASING OPTIONS -> OPERATING UNIT -> V1 VISION OPERATIONS.
REQUISITION NUMBER: MANUAL ALPHANUMERIC.
RFQ(REQUEST FOR QUOTATION): 3 TYPES.
===========================
1.BID RFQ 2.CATALOG RFQ 3.STANDARD RFQ.
1.BID:
======
WE WILL CREATE BID RFQ FOR FIXED ITEMS AND FIXED QUANTITY.
IN BID RFQ NO PRICE BREAKS(DISCOUNTS) ARE AVAILABLE.
SHIPMENTS WILL COME ONLY FOR BID RFQ.
2.CATALOG:
==========
WE WILL CREATE CATALOG RFQ FOR THE ITEMS WHICH WE WILL PURCHASE REGULARLY.
IT CONTAINS PRICE BREAKS(DISCOUNTS).
3.STANDARD:
===========
WE WILL CREATE STANDARD RFQ FOR THE ITEMS WHICH WE WILL PURCHASE ONLY ONCE THAT IS NOT VERY OFTEN.
IT CONTAINS PRICE BREAKS(DISCOUNTS).
---> GO TO PURCHASE,VISION OPERATIONS (USA) RESPONSIBILITY.
NAVIGATION:
------------
->RFQS AND QUOTATIONS -> RFQS ->ENTER THE DETAILS AT HEADER LEVEL.
IN RFQ HEADERS LEVEL THERE IS TWO FIELDS CALLED "DUE DATE" AND "CLOSE DATE",ALWAYS "CLOSE DATE" IS TO BE HIGHER THAN
THE "DUE DATE"
HERE "DUE DATE" MEANS WHEN BUYER GOING TO PAY THE AMOUNT AND "CLOSE DATE" IS WHEN WE CLOSE THE DEAL. THAT MEANS
BEFORE CLOSING THE DEAL WE SHOULD PAY THE WHOLE AMOUNT WITHOUT ANY DUE. SO "CLOSE DATE" ALWAYS
HIGHER THAN THE "DUE DATE".
->ENTER THE LINES LEVEL
->TERMS(BUTTON)->ENTER THE DETAILS
->CURRENCY(BUTTON)->ENTER THE CONVERSION RATE(IF THE SUPPLIER CURRENCY AND BUYER CURRENCY IS DIFFERENT.)
->PRICE BREAKS(BUTTON)->ENTER THE DETAILS.
AND U CAN ENTER THE EXPECTING DISCOUNT PERCENTAGE IN THE DISCOUNT FIELD.
AND U CAN TELL FIRST LINE QUANTITY WHEN U WANT, BY MENTIONING FROM_DATE AND TO_DATE
->CTRL+S.
->SUPPLIERS(BUTTON)->ENTER THE DETAILS AND SAVE THE DETAILS. HERE SUPPLIER DETAILS WILL SAVE IN "PO_RFQ_VENDORS" TABLE
WE CAN QUERY THE DATA BY PASSING PO_HEADER_ID IN WHERE CLAUSE.
EX: SELECT * FROM PO_RFQ_VENDORS WHERE PO_HEADER_ID=110708.
TO CONFIGURE SUPPLIER NAMES IN "ADD FROM LIST"(BUTTON):
========================================================
NAVIGATION:
---------------
->SUPPLIER BASE ->SUPPLIER LIST ->U CAN ADD NEW SUPPLIERS HERE.
COPY THE RFQ NUMBER.
HEADERS LEVEL TABLE:
--------------------
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='311'--IN THIS TABLE RFQ,QUOTATIONAND PO DETAILS WILL STORE.
AND TYPE_LOOKUP_CODE='RFQ'--SO WE ARE GIVING TYPE_LOOKUP_CODE.BUT STILL WE CAN GET DUPLICATE DATA.
AND ORG_ID='204'--SO WE ARE GIVING ORG_ID
--PO_HEADER_ID IS PK.110686.
LINES LEVEL TABEL:
-----------------
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=110686--PO_LINE_ID IS PK. 173615.
PRICE BREAKS LEVEL:
--------------------
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID='173615'--LINE_LOCATION_ID IS PK. 264801 AND 264802.
ONCE WE SEND THE RFQS TO SUPPLIERS THEN WE WILL RECEIVE THE QUOTATIONS FROM THE SUPPLIERS.
08-OCT-2014:
-------------
RFQ HEADERS LEVEL:
=================
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='314'
AND TYPE_LOOKUP_CODE='RFQ'
AND ORG_ID='204'
SELECT * FROM PO_AGENTS WHERE AGENT_ID=25
SELECT * FROM PER_ALL_PEOPLE_F WHERE PERSON_ID='25'
TO SEE THE SUPPLIERS DATA:
---------------------------
SELECT * FROM PO_RFQ_VENDORS WHERE PO_HEADER_ID=110708.
RFQ LINES LEVEL:
================
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID='110708'--PO_LINE_ID IS PK.173637--ORG_ID,ITEM_ID.
select * from mtl_categories where category_id=1
select * from mtl_units_of_measure where UNIT_OF_MEASURE='Each'
select * from po_line_types where line_type_id=1
SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID=217955--INVENTORY_ITEM_ID and ORGANIZATION_ID.
SELECT * FROM ORG_ORGANIZATION_DEFINITIONS --ORGANIZATION_ID.
SHIPMENTS AND PRICE BREAKS:
============================
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=173637--LINE_LOCATION_ID IS PK.
SELECT * FROM AP_TERMS WHERE TERM_ID=10002.
QUOTATIONS:
==========
3 TYPES.
BID,CATALOG,STANDARD THESE 3 ARE SAME AS LIKE RFQ.
NAVIGATION:
===========
GO TO PURCHASIING,VISION OPERATIONS(USA).
->RFQS AND QUOTATIONS ->QUOTATIONS ->ENTER THE DETAILS.
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='505'
AND TYPE_LOOKUP_CODE='QUOTATION'
AND ORG_ID=204
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=110726
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=173655
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='506'
AND TYPE_LOOKUP_CODE='QUOTATION'
AND ORG_ID=204
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=110727
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=173656
WE CAN FINDOUT THE QUOTATION IS IT CREATED BASED ON RFQ OR NOT ?, BY USING THE COLUMN "FROM_TYPE_LOOKUP_CODE"
IN PO_HEADERS_ALL TABLE.
AND WE CAN FINDOUT BASED ON WHICH RFQ THIS QUOTATION IS CREATED. BY USING THE COLUMN "FROM_HEADER_ID".
TAKE "FROM_HEADER_ID" VALUE AND QUERY FROM PO_HEADERS_ALL
EX:
===
SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID='110728'
SO QUOTATION 507 IS CREATED BASED ON THE RFQ 317.
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='507'
AND TYPE_LOOKUP_CODE='QUOTATION'
AND ORG_ID=204
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=110729
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=173658
PURCHASING ORDER:
===================
4 TYPES.
1.STANDARD PURCHASE ORDER.
2.PLANNED PURCHSE ORDER.
3.BLANKET PURCHSE AGREEMENT.
4.CONTRACT PURCHSE AGREEMENT.
AGREEMENT:
----------
IT MEANS WE WILL PURCHSE ITEMS OR SERVICES IN FUTURE ACCORDING TO THE AGREEMENT.
THE PURCHSE ORDER WE CAN ENTER IN 4 LEVELS THAT ARE
1.HEADERS 2.LINES 3.SHIPMENTS 4.DISTRIBUTIONS
TO CREATE PURCHSE ORDER
GO TO PURCHASING,VISION OPERATIONS (USA) RESPONSIBILITY.
WE CAN CREATE THE PO AUTOMATICALLY BASED ON THE REQUISITION BY USING AUTOCREATE OPTION.
NAVIGATION:
----------- ->AUTOCREATE ->ENTER THE DETAILS.
HOW TO FIND PO CREATED BASED ON REQUISITION NUMBER?. FROM FRONT END.
-----------------------------------------------------
GO TO PO DISTRIBUTION ->MORE TAB ->NUMBER. THIS IS REQUISITION NUMBER.
HOW TO FIND PO CREATED BASED ON REQUISITION NUMBER?. FROM BACKEND.
-------------------------------------------------------------------
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6056'
IN "DOCUMENT_CREATION_METHOD" COLUMN IT WILL SHOW HOW THIS DOCUMENT CREATED.
NAVIGATION:
===========
->PURCHASE ORDERS ->PURCHASE ORDERS ->ENTER DETAILS. ->CTRL+S. ->COPY PONUMBER ->APPROVE.
HEADERS TABLE:
--------------
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6056'--PO_HEADER_ID IS PK.
LINES TABLE:
-------------
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=110746--PO_LINE_ID IS PK.
SHIPMENT TABLE:
---------------
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=173675--LINE_LOCATION_ID IS PK.
DISTRIBUTIONS TABLE:
--------------------
SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=264861--PO_DUSTRIBUTION_ID IS PK.
PO RELEASES TABLE:
------------------
PURHASE ORDER RELEASES NEED ONLY FOR PLANNED PURCHASE ORDER AND BLANKET PURCHSE AGREEMENT,SO CREATE RELEASES ONLY FOR
PO NUMBERS WHICH ARE BELONGS TO PLANNED AND BLANKET PURCHASE ORDERS.
->PURCHASIE ORDERS ->RELEASES ->ENTER DETAILS ->DISTRIBUTIONS ->CTRL+S ->APPROVE. ->GO TO SRS
->FIND LATEST->LOG->m_documentId:131281. HERE M_DOCUMENT_ID ID NOTHING BUT THE PO_RELEASE_ID IN PO_RELEASES_ALL TABLE.
SELECT * FROM PO_RELEASES_ALL WHERE PO_RELEASE_ID=131281--PO_RELEASE_ID IS PK.
TO SEE THE TYPE NAME:
---------------------
select * from po_document_types_all where document_type_code='PO'
AND DOCUMENT_SUBTYPE='STANDARD'
AND ORG_ID=204
NUMBERS AND DATES ARE SAVED IN MAIN TABLE.
WE CAN FIND OUT SUPPLIER CONTACT NAME FROM AP_SUPPLIER_CONTACTS BASED ON VENDOR_CONTACT_ID IN PO_HEADERS_TABLE.
TO SEE LOCATION NAMES:
-----------------------
SELECT * FROM HR_LOCATIONS--LOCATION_ID IS PK.
CURRENCY MAIN TABLE:
=---------------------
SELECT * FROM FND_CURRENCIES WHERE CURRENCY_CODE='USD'
TO SEE BUYER NAME:
==================
BUYER IS NOTHING BUT AGENT.
AGENT_ID IN HEADERS TABLE. BUT AGENT_ID MAIN TABLE IS PO_AGENTS.
SELECT * FROM PO_AGENTS--AGENT_ID IS PK.
AGENT_ID IN PO_AGENTS AND PERSON_ID IN PER_ALL_PEOPLE_F ARE SAME DATA.
TO SEE BUYERNAME:
------------------
SEE FULL_NAME COLUMN IN BELOW TABLE.
SELECT * FROM PER_ALL_PEOPLE_F WHERE PERSON_ID='25'--PERSON_ID IS PK.
LINES LEVEL:
=============
LINE_TYPE_ID IS SAVING IN LINES TABLE.
TO SEE LINE_TYPE:
----------------
SEE LINE_TYPE COLUMN IN BELOW TABLE.
SELECT * FROM PO_LINE_TYPES WHERE LINE_TYPE_ID=1
TO SEE ITEM NAME:
--------------------
ITEM_ID IS SAVING IN LINES TABLE
SELECT * FROM MTL_SYSTEM_ITEMS_B,PO_LINES_ALL PLA WHERE INVENTORY_ITEM_ID=PLA.ITEM_ID AND ORGANIZATION_ID=204
REVISIONS MAIN TABLE:
======================
ITEM_REVISION IN LINES TABLE.
SELECT * FROM MTL_ITEM_REVISIONS WHERE INVENTORY_ITEM_ID=217955 AND ORGANIZATION_ID=204
CATEGORIES MAIN TABLE:
=======================
CATEGORY_ID IN LINES TABLE.
SELECT * FROM MTL_CATEGORIES WHERE CATEGORY_ID=1 --CATEGORY_ID IS PK.
UOMS PARENT TABLE:
====================
UNIT_MEAS_LOOKUP_CODE IN LINES TABLE.
SELECT * FROM MTL_UNITS_OF_MEASURE WHERE UNIT_OF_MEASURE='Each'--(PO_LINE_ALL.UNIT_MEAS_LOOKUP_CODE).--UOM CODE IS PK.
SHIPMENTS TABLE:
==================
TO SEE SHIP TO ORGANIZATION NAME.
SHIP_TO_ORGANIZATION_ID IN SHIPMENTS TABLE
SELECT ORGANIZATION_NAME FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_ID=204--PO_LINE_LOCATIONS_ALL.SHIP_TO_ORGANIZATION_ID
--ORGANIZATION_ID IS PK.
FAQ:
======
SHIPMENTS ->MORE TAB -> APPROVAL.
2-WAY:
--------
COMPARE 2 DOCUMENTS.
1.PO 2.INVOICE IS NOTHING BUT BILL FOR ORDER QUANTITY.
EX:
--
PO FOR 40 ITEMS AND INVOICE FOR 40 ITEMS.
3-WAY:
=======
COMPARE 3 DOCUMENTS.
1.PO 2.RECEIPT 3.INVOICE IS NOTHING BUT BILL FOR RECEIVED QUANTITY.
PO FOR 40 ITEMS ,RECEIVED 30 ITEMS, SO INVOICE FOR 30 ITEMS. THAT IS INVOICE FOR RECEIVED QUANTITY.
4-WAY:
=======
COMPARE 4 DOCUMENTS.
1.PO 2.RECEIPT 3.INSPECTION 4.INVOICE IS NOTHING BUT BILL FOR ACCEPTED QUANTITY.
PO FOR 40 ITEMS , RECEIVED 30 ITEMS , INSPECTION 25 ITEMS AND INVOICE FOR 25 ITEMS. THAT IS
INVOICE FOR ACCEPTED QUANTITY. THE REMAINING 5 ITEMS FAILED IN INSPECTION WILL RETURN TO SUPPLIERS.
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6056'--STANDARD.
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6057'--PLANNED.
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6058'--BLANKET.FOR BLANKET INSTEAD OF SHIPMENTS PRICE BREAKS ARE AVAILABLE.
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6059'--CONTRACT THERE WON'T BE ANY THING EXCEPT TERMS AND CONDITIONS.
RECEIPT:
==========
GO TO PURCHASING,VISION OERATIONS USA RESP
NAVIGATION:
----------
RECEVING ->RECEIPTS ->V1 ->ENTER THE DETAILS. ->CTRL+S ->COPY RECIPT_NUMBER. ->CLOSE IT. ->IT WILL SHOW LINES
->CHECK IT AND SAVE IT.
WE CAN GET REQUEST_ID FROM SRS WINDOW AFTER CREATION OF THE RECEIPT.
SELECT * FROM RCV_TRANSACTIONS WHERE REQUEST_ID=6003764--TRANSACTION_ID IS PK.
WE CAN FIND THE SHIPMENT_HEADER_ID IN SRS WINDOW 6003765 LOG FILE ARGUMENTS.
WE CAN FIND THE SHIPMENT_HEADER_ID IN RCV_TRANSACTIONS TABLE.
SELECT * FROM RCV_SHIPMENT_HEADERS WHERE SHIPMENT_HEADER_ID=4817907
(OR)
WE CAN FIND THE SHIPMENT_HEADER_ID BY USING THE RECEIPT_NUM AFTER SAVING THE RECEIPT.
SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM='8466' AND ORGANIZATION_ID=204--SHIPMENT_HEADER_ID IS PK.
SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=4817907--SHIPMENT_LINE_ID IS PK.
AP (ACCOUNT PAYABLE):
======================
INVOICES:
=============
TO CREATE INVOICE
GO TO PAYABLES, VISION OPERATIONS (USA).
NAVIGATION:
============
INVOICES ->ENTRY ->INVOICES ->ENTER THE DETAILS. AND TAX CONTROL AMOUNT 0.00
THEN GO FOR LINES. ->ENTER THE DETAILS.
THEN GO FOR ALL DISTRIBUTIONS ->ENTER THE DETAILS. ->CTRL+S. ->ACTIONS..1 ->VALIDATE ->CREATE ACCOUNTING->FINAL POST.
COPY INVOICE NUMBER.
HEADERS TABLE:
==============
SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='inv1' AND VENDOR_ID=37170 --INVOICE_ID IS PK.
NOTE:
===== WHEN WE HAVE DUPLICATE INVOICE NUMBERS BUT SUPPLIER WILL BE DIFFERENT.
LINES TABLE:
=============
SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=211420
DISTRIBUTIONS TABLE:
====================
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=211420 COPY ACCOUNTING_EVENT_ID.3330713
ACCOUNTS:
=========
SELECT * FROM XLA_EVENTS WHERE EVENT_ID=3330713-- EVENT_ID IS PK.
HERE "ACCOUNTING_EVENT_ID" IN AP_INVOICE_DISTRIBUTIONS_ALL AND "EVENT_ID" IN XLA_EVENTS ARE SAME.
SELECT * FROM XLA_EVENTS WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM XLA_EVENTS GROUP BY EVENT_ID)
SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID=3330713--AE_HEADER_ID IS PK.
AE_HEADER_ID=4950976
SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=4950976 --GL_SL_LINK_ID 6857054,6857053
SEE THE GL_SL_LINK_ID COLUMN IN ABOVE TABLE.
GL TABLES:
===========
GL_INTERFACE BACK END:
----------------------
FROM BACK END WE CAN QUERY BY USING GL_SL_LINK_ID.
THE GL_SL_LINK_ID IN XLA_AE_LINES AND GL_INTERFACE TABLES ARE SAME. SO WE CAN QUERY THE DATA FROM BACKEND
BY USING "GL_SL_LINK_ID" COLUMN.
SELECT * FROM GL_INTERFACE WHERE GL_SL_LINK_ID=6857053
SELECT * FROM GL_INTERFACE WHERE GL_SL_LINK_ID=6857054
(OR)
GL_INTERFACE FRONT END:
-----------------------
WE CAN QUERY THE DATA FROM GL_INTERFACE BY USING REQUEST_ID OR GROUP_ID
WHICH IS PRESENT IN OUTPUT FILE OF Journal Import IN SRS WINDOW.
SELECT * FROM GL_INTERFACE WHERE REQUEST_ID=6003803
OR
SELECT * FROM GL_INTERFACE WHERE GROUP_ID=1549007
WE CAN QUERY THE DATA IN GL_JE_HEADERS BY USING "JE_HEADER_ID" OR "JE_BATCH_ID" PRESENT IN THE "GL_INTERFACE" TABLE.
SELECT * FROM GL_JE_HEADERS WHERE JE_HEADER_ID=4685155--JE_HEADER_ID IS PK.
SELECT * FROM GL_JE_LINES WHERE JE_HEADER_ID=4685155
GL_JE_BATCHES BACK END:
------------------------
FROM BACKEND WE CAN QUERY THE DATA FROM GL_JE_BATCHES BY USING THE JE_BATCH_ID PRESENT IN THE "GL_INTERFACE"
TABLE OR "GL_JE_HEADERS" TABLE.
SELECT * FROM GL_JE_BATCHES WHERE JE_BATCH_ID=2931184.
GL_JE_BATCHES FRONT END:
-----------------------
WE CAN QUERY THE GL_JE_BATCHES FROM FRONT END BY USING THE REQUEST_ID OF Posting: Single Ledger.
SELECT * FROM GL_JE_BATCHES WHERE REQUEST_ID=6003805
GL_BALANCES:
==============
IT SHOWS THE TOTAL BALANCES.
THESE CODE_COMBINATION_ID VALUES OF THESE TWO(12854,13402) ARE PRESENT IN "CODE_COMBINATION_ID" COLUMN OF
GL_INTERFACE OR GL_JE_LINES TABLES.
SELECT * FROM GL_BALANCES WHERE CODE_COMBINATION_ID=12854
SELECT * FROM GL_BALANCES WHERE CODE_COMBINATION_ID=13402
AP_PAYMENT_SCHEDULES_ALL:
=========================
AP_PAYMENT_SCHEDULES_ALL TABLE WE WILL USE TO KNOW THE "INVOICE DUE DATE" AND "AMOUNT_REMAINING".
WE QUERY THE DATA FROM "AP_PAYMENT_SCHEDULES_ALL" BY USING "INVOICE_ID".
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID=211420
DISTRIBUTION SET:
===================
TO CREATE DISTRIBUTION SET GO TO PAYABLES, VISION OPERATIONS(USA) RESP.
NAVIGATION:
==========
SETUP ->INVOICES ->DISTRIBUTION SET ->ENTER THE DETAILS. ->CTRL+S.
AND WE CAN USE IT IN INVOICES.
SELECT * FROM AP_DISTRIBUTION_SETS_ALL WHERE DISTRIBUTION_SET_NAME='SR_DISTRIBUTION_SET'
SELECT * FROM AP_DISTRIBUTION_SET_LINES_ALL WHERE DISTRIBUTION_SET_ID=11249
AP_HOLDS_ALL:
==============
AP_HOLDS_ALL TABLE WE WILL USE TO KNOW THE HOLD INVOICES.
WE CAN QUERY THE DATA FROM AP_HOLDS_ALL BY USING INVOICE_ID.
SELECT * FROM AP_HOLDS_ALL WHERE INVOICE_ID=211420--COPY RELEASE_LOOKUP_CODE.
SELECT * FROM AP_HOLDS_RELEASE_NAME_V WHERE RELEASE_LOOKUP_CODE='APPROVED'
REQUIREMENT:
==============
INVOICENUM,TYPE,CDATE,SUPPLIER NAME,SITE CODE,AMOUNT,DUE DATE
SELECT APIA.INVOICE_ID,
APIA.INVOICE_NUM,
APIA.INVOICE_TYPE_LOOKUP_CODE TYPE,
APIA.CREATION_DATE CDATE,
APS.VENDOR_NAME,
APSSA.VENDOR_SITE_CODE "SITE CODE",
APIA.INVOICE_AMOUNT AMOUNT,
APSA.DUE_DATE,
APSA.AMOUNT_REMAINING "DUE AMOUNT"
FROM AP_INVOICES_ALL APIA,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL APSSA,
AP_PAYMENT_SCHEDULES_ALL APSA
WHERE APIA.INVOICE_NUM='inv1'
AND APIA.VENDOR_ID=APS.VENDOR_ID
AND APIA.VENDOR_SITE_ID=APSSA.VENDOR_SITE_ID
AND APIA.INVOICE_ID=APSA.INVOICE_ID
PAYMENTS:
==========
3 TYPES:
==========
1.MANUAL 2.QUICK 3.REFUND.
1.MANUAL:
=========
MANUALLY PAY THE CASH/CHEQUE AND ENTER THE DETAILS IN THE SYSTEM.
2.QUICK:
=======
GENERATE THE CHECK PRINTING AUTOMATICALLY THROUGH THE SYSTEM.
3.REFUND:
==========
IF ANY REFUNDABLE AMOUNT ARE THERE THEN WE WILL PAY THE AMOUNT.
DEVELOP INVOICE AND COPY THE INVOICE NUMBER
PAYMENTS:
=========
BANK ACCOUNT:BOFA_204.
PAYMENT:CHECK
PAYMENT PROCESS PROFILE:CHECK_POSITIVE PAY A.
ENTER/ADJUST INVOICE:
=======================
INVOICE NUMBER: CTRL+V. ->CTRL+S AND CLOSE.
ACTIONS1 -> CREATE ACCOUNTING->FINAL POST.
WE CAN ALSO PAY THE AMOUNT DIRECTLY FROM INVOICE.
IN INVOICE ->AFTER CREATE ACCOUNTING FINAL POST.
ACTION1 -> CHECK PAY IN FULL
TABLES:
=======
SELECT * FROM IBY_PAYMENTS_ALL
SELECT * FROM IBY_EXTERNAL_PAYEES_ALL ->STORES PAYEE(SUPPLIER) INFORMATION.
SELECT * FROM IBY_PAY_SERVICE_REQUESTS ->PAYMENT PROCESS REQUESTS(PPR) INFORMATION.
CE_BANK_ACCOUNTS/AP_BANK_ACCOUNTS_ALL.
CE_BANK_ACCT_USES_ALL/AP_BANK_ACCOUNT_USES_ALL
CE_PAYMENT_DOCUMENTS/AP_CHECK_STOCKS_ALL.
XLA_DISTRIBUTION_LINKS
TAX TABLES:
============
ZX_LINES->DETAIL TAX LINES FOR THE INVOICE.
ZX_LINES_SUMMARY ->SUMMARY TAX LINES FOR THE INVOICE.
ZX_REC_NREC_DIST -> TAX DISTRIBUTION FOR THE INVOICE.
ZX_LINES_DET_FACTORS ->TAX DETERMINATION FACTORS FOR THE INVOICE.
IF IN INVOICE IS CREATED BASED ON THE CREATE ACCOUNTING WITH FINAL.
THAT MEANS
ACTIONS..1 ->VALIDATE ->CREATE ACCOUNTING->FINAL
NAVIGATION:
============
INVOICES ->ENTRY ->INVOICES ->ENTER THE DETAILS. AND TAX CONTROL AMOUNT 0.00
THEN GO FOR LINES. ->ENTER THE DETAILS.
THEN GO FOR ALL DISTRIBUTIONS ->ENTER THE DETAILS. ->CTRL+S. ->ACTIONS..1 ->VALIDATE ->CREATE ACCOUNTING->FINAL.
COPY INVOICE NUMBER.
HEADERS TABLE:
==============
SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='INV3' AND VENDOR_ID=37170 --INVOICE_ID IS PK.
NOTE:
===== WHEN WE HAVE DUPLICATE INVOICE NUMBERS BUT SUPPLIER WILL BE DIFFERENT.
LINES TABLE:
=============
SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=211422
DISTRIBUTIONS TABLE:
====================
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=211422 --COPY ACCOUNTING_EVENT_ID.
ACCOUNTS:
=========
SELECT * FROM XLA_EVENTS WHERE EVENT_ID=3330714--EVENT_ID IS PK.
HERE "ACCOUNTING_EVENT_ID" IN AP_INVOICE_DISTRIBUTIONS_ALL AND "EVENT_ID" IN XLA_EVENTS ARE SAME.
SELECT * FROM XLA_EVENTS WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM XLA_EVENTS GROUP BY EVENT_ID)
SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID=3330714--AE_HEADER_ID IS PK.
AE_HEADER_ID=4950977
SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=4950977 --GL_SL_LINK_ID 66857056,6857055
SEE THE GL_SL_LINK_ID COLUMN IN ABOVE TABLE.
SELECT*FROM GL_INTERFACE WHERE GL_SL_LINK_ID=6857056
SELECT*FROM GL_INTERFACE WHERE GL_SL_LINK_ID=6857055
WE CAN NOT QUERY THE DATA FROM GL_INTERFACE
BY USING THE GL_SL_LINK_ID BECAUSE WHEN WE DO ONLY FINAL THE DATA DID NOT COME INTO GL_INTERFACE TABLE.
FOR THAT WE HAVE TO DO PAYMENT WITH CREATE ACCOUNTING WITH FINAL POST.
SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='INV3' AND VENDOR_ID=37170
SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=211422
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=211422
PAYMENTs TABLE:
==============
SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=211422--INVOICE_PAYMENT_ID IS PK.
COPY ACCOUNTING_EVENT_ID 3330715.
select * from ap_invoice_payments_all where invoice_id=211422--copy check_id=77610
select * from ap_payment_schedules_all where invoice_id=211422
select * from ap_checks_all where check_id=77610 --check_id id pk.
select * from ap_checks_all where rowid not in (select min(rowid) from ap_checks_all group by check_id)
ACCOUNTING TABLES:
===================
SELECT * FROM XLA_EVENTS WHERE EVENT_ID=3330715
SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID=3330715 --AE_HEADER_ID IS PK.
SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=4950978 --GL_SL_LINK_ID 6857058,6857057
GL_SL_LINK_ID IN ABOVE TABLE.
GL TABLES:
==========
FROM BACKEND:
-------------
SELECT * FROM GL_INTERFACE WHERE GL_SL_LINK_ID=6857057
OR
SELECT * FROM GL_INTERFACE WHERE GL_SL_LINK_ID=6857058
FROM FRONT END:
----------------
SELECT * FROM GL_INTERFACE WHERE REQUEST_ID=6003836
OR
SELECT * FROM GL_INTERFACE WHERE GROUP_ID=1549008
GL HEADERS TABLE:
=================
SELECT * FROM GL_JE_HEADERS WHERE JE_HEADER_ID=4685158--JE_HEADER_ID IS PK.
GL LINES TABLE:
===============
SELECT * FROM GL_JE_LINES WHERE JE_HEADER_ID=4685158
GL_JE_BATCHES TABLE:
=====================
BACK END:
=========
SELECT * FROM GL_JE_BATCHES WHERE JE_BATCH_ID=2931187--JE_BATCH_ID IS PK.
FRONT END:
==========
REQUEST ID IS GOT FROM Posting: Single Ledger IN SRS WINDOW.
SELECT * FROM GL_JE_BATCHES WHERE REQUEST_ID=6003837
GL_BALANCE:
============
IT SHOWS THE TOTAL BALANCES.
SELECT * FROM GL_BALANCES WHERE CODE_COMBINATION_ID=12831
SELECT * FROM GL_BALANCES WHERE CODE_COMBINATION_ID=12854
AP_PAYMENT_SCHEDULES_ALL:
=========================
AP_PAYMENT_SCHEDULES_ALL TABLE WE WILL USE TO KNOW THE "INVOICE DUE DATE" AND "AMOUNT_REMAINING".
WE QUERY THE DATA FROM "AP_PAYMENT_SCHEDULES_ALL" BY USING "INVOICE_ID".
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID=211422
DISTRIBUTION SET:
===================
TO CREATE DISTRIBUTION SET GO TO PAYABLES, VISION OPERATIONS(USA) RESP.
NAVIGATION:
==========
SETUP ->INVOICES ->DISTRIBUTION SET ->ENTER THE DETAILS. ->CTRL+S.
AND WE CAN USE IT IN INVOICES.
SELECT * FROM AP_DISTRIBUTION_SETS_ALL WHERE DISTRIBUTION_SET_NAME='SR_DISTRIBUTION_SET'
SELECT * FROM AP_DISTRIBUTION_SET_LINES_ALL WHERE DISTRIBUTION_SET_ID=11249
AP_HOLDS_ALL:
==============
AP_HOLDS_ALL TABLE WE WILL USE TO KNOW THE HOLD INVOICES.
WE CAN QUERY THE DATA FROM AP_HOLDS_ALL BY USING INVOICE_ID.
SELECT * FROM AP_HOLDS_ALL WHERE INVOICE_ID=211422--COPY RELEASE_LOOKUP_CODE.
SELECT * FROM AP_HOLDS_RELEASE_NAME_V WHERE RELEASE_LOOKUP_CODE='APPROVED'
