Wednesday 29 June 2011

purchase to invoice link

SELECT   poh.org_id "ORGANIZATION", poh.segment1 "PO NO",
         poh.creation_date "PO DATE", SUM (pol.quantity) "PO QUANTITY",
         rch.receipt_num "GRN NO", rct.transaction_date "GRN DATE",
         SUM (pll.quantity_received) "RECEIVED QTY",
         SUM (pll.quantity_accepted) "ACCEPTED QTY",
         SUM (ail.quantity_invoiced) "INVOICED QTY",
         aia.amount_paid "PAYMENT AMOUNT", apc.check_date "DATE OF PAYMENT",
         apt.NAME "PAYMENT_TERM",
         pol.ITEM_ID
FROM     po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all pll,
         po_distributions_all pda,
         rcv_shipment_headers rch,
         rcv_shipment_lines rcl,
         rcv_transactions rct,
         ap_invoice_lines_all ail,
         ap_invoices_all aia,
         ap_invoice_distributions_all aid,
         ap_checks_all apc,
         ap_invoice_payments_all aip,
         ap_terms apt
WHERE    poh.po_header_id = pol.po_header_id
AND      pol.item_id IN (SELECT distinct inventory_item_id
                         FROM mtl_system_items_b
                         WHERE segment1 BETWEEN NVL (:from_item_code, segment1)
                         AND NVL (:to_item_code, segment1))
         AND pol.po_line_id = pll.po_line_id
         AND pll.line_location_id = pda.line_location_id
         AND pda.po_distribution_id = rcl.po_distribution_id
         AND rch.shipment_header_id = rcl.shipment_header_id
         AND rcl.shipment_line_id = rct.shipment_line_id
         AND ail.invoice_id = aia.invoice_id
         AND aia.invoice_id = aid.invoice_id
         AND rct.transaction_id = aid.rcv_transaction_id
         AND aip.invoice_id(+) = aia.invoice_id
         AND aip.check_id = apc.check_id(+)
         AND apt.term_id = aia.terms_id
         AND TRUNC (rct.transaction_date) BETWEEN NVL (:from_date,
                                                   rct.transaction_date
                                                  )
                                          AND NVL (:TO_DATE,
                                                   rct.transaction_date
                                                  )
         AND poh.segment1 = nvl(:po_num,poh.segment1)
         AND poh.org_id = NVL (:org_id, poh.org_id)
GROUP BY poh.org_id,
         poh.segment1,
         poh.creation_date,
         rch.receipt_num,
         aia.amount_paid,
         rct.transaction_date,
         apc.check_date,
         apt.NAME,
         pol.ITEM_ID

=================================================================

select prha.SEGMENT1 req_no,
       prha.CREATION_DATE req_date,
       pv.VENDOR_NAME,
       pha.SEGMENT1 po_no,
       pha.CREATION_DATE po_date,
       prla.ITEM_DESCRIPTION,
       sum(pla.QUANTITY) po_qty,
       pla.UNIT_PRICE,
       rsh.RECEIPT_NUM,
       rt.TRANSACTION_DATE rec_date,
       rt.TRANSACTION_TYPE,
       aia.INVOICE_NUM,
       aia.INVOICE_DATE,
       aia.INVOICE_AMOUNT,
       aipa.PAYMENT_BASE_AMOUNT,
       aipa.AMOUNT,
       apsa.PAYMENT_METHOD_CODE,
       aca.CHECK_NUMBER,
       aca.CHECK_DATE
from po_requisition_headers_all prha,
     po_requisition_lines_all prla,
     po_req_distributions_all prda,
     po_distributions_all pda,
     po_lines_all pla,
     po_headers_all pha,
     po_vendors pv,
     rcv_transactions rt,
     rcv_shipment_headers rsh,
     ap_invoice_distributions_all aida,
     ap_invoice_lines_all aila,
     ap_invoices_all aia,
     ap_invoice_payments_all aipa,
     ap_payment_schedules_all apsa,
     ap_checks_all aca
where prha.REQUISITION_HEADER_ID=prla.REQUISITION_HEADER_ID
and   prla.REQUISITION_LINE_ID=prda.REQUISITION_LINE_ID
and   pda.REQ_DISTRIBUTION_ID=prda.DISTRIBUTION_ID
and   pla.PO_LINE_ID=pda.PO_LINE_ID
and   pha.PO_HEADER_ID=pla.PO_HEADER_ID
and pv.VENDOR_ID=pha.VENDOR_ID
and pda.PO_DISTRIBUTION_ID=rt.PO_DISTRIBUTION_ID
and rsh.SHIPMENT_HEADER_ID=rt.SHIPMENT_HEADER_ID
and rt.TRANSACTION_ID=aida.RCV_TRANSACTION_ID
and aila.INVOICE_ID=aida.INVOICE_ID
and aia.INVOICE_ID=aila.INVOICE_ID
and aia.INVOICE_ID=aipa.INVOICE_ID
and aia.INVOICE_ID=apsa.INVOICE_ID
and aca.CHECK_ID=aipa.CHECK_ID
and pha.SEGMENT1='7162'--nvl(:p_po_no,pha.SEGMENT1)
group by prha.SEGMENT1,prha.CREATION_DATE,pv.VENDOR_NAME,
pha.SEGMENT1 ,pha.CREATION_DATE ,
prla.ITEM_DESCRIPTION,pla.UNIT_PRICE,
rsh.RECEIPT_NUM, rt.TRANSACTION_DATE ,rt.TRANSACTION_TYPE,
aia.INVOICE_NUM,aia.INVOICE_DATE,aia.INVOICE_AMOUNT,
aipa.PAYMENT_BASE_AMOUNT,aipa.AMOUNT,apsa.PAYMENT_METHOD_CODE,
aca.CHECK_NUMBER,aca.CHECK_DATE

Tuesday 28 June 2011

ORACLE REPORT DOCUMENT

http://www.oracle-educate.com/Reports_guide_to_changed_functionality.pdf

Another code to Convert column to row

 Run The Function and find the different of Each Query


create table retail_sale
(
 retail_sale_id number not null
,terminal_id number not null
,sale_date date not null
,currency_category_code varchar2(8) -- base, original
,ttl_amt number
,ttl_dis number
,ttl_tax number
)


select * from retail_sale

create unique index retail_sale_pk on retail_sale (retail_sale_id)
/
create unique index retail_sale_uk1 on retail_sale (terminal_id,sale_date,currency_category_code)
/

alter table retail_sale
   add constraint retail_sale_pk primary key (retail_sale_id)
   add constraint retail_sale_uk1 unique (terminal_id,sale_date,currency_category_code)
/

insert into retail_sale values (1,1,trunc(sysdate),'ORIGINAL',100,20,4.80);
insert into retail_sale values (2,1,trunc(sysdate),'BASE',100*1.17,20*1.17,round(4.8*1.17,2));



select *
from retail_sale
/

select
       terminal_id
      ,sale_date
      ,case when currency_category_code = 'BASE'     then ttl_amt end base_ttl_amt
      ,case when currency_category_code = 'BASE'     then ttl_dis end base_ttl_dis
      ,case when currency_category_code = 'BASE'     then ttl_tax end base_ttl_tax
      ,case when currency_category_code = 'ORIGINAL' then ttl_amt end original_ttl_amt
      ,case when currency_category_code = 'ORIGINAL' then ttl_dis end original_ttl_dis
      ,case when currency_category_code = 'ORIGINAL' then ttl_tax end original_ttl_tax
from retail_sale
/

select
       terminal_id
       ,sale_date
      ,sum(case when currency_category_code = 'BASE'     then ttl_amt end) base_ttl_amt
      ,sum(case when currency_category_code = 'BASE'     then ttl_dis end) base_ttl_dis
      ,sum(case when currency_category_code = 'BASE'     then ttl_tax end) base_ttl_tax
      ,sum(case when currency_category_code = 'ORIGINAL' then ttl_amt end) original_ttl_amt
      ,sum(case when currency_category_code = 'ORIGINAL' then ttl_dis end) original_ttl_dis
      ,sum(case when currency_category_code = 'ORIGINAL' then ttl_tax end) original_ttl_tax
from retail_sale
group by
       terminal_id
      ,sale_date
/


select retail_sale_id
      ,terminal_id
      ,sale_date
      ,currency_category_code
      ,case
           when dollar_type.rowno = 1 then 'TTL_AMT'
           when dollar_type.rowno = 2 then 'TTL_DIS'
           when dollar_type.rowno = 3 then 'TTL_TAX'
       end dollar_name
      ,case
           when dollar_type.rowno = 1 then ttl_amt
           when dollar_type.rowno = 2 then ttl_dis
           when dollar_type.rowno = 3 then ttl_tax
       end amt
from retail_sale
    ,(
      select rownum rowno
      from dual
      connect by level <=3
     ) dollar_type
order by 1,2,3,5



select terminal_id
      ,sale_date
      ,case
           when dollar_type.rowno = 1 then 'TTL_AMT'
           when dollar_type.rowno = 2 then 'TTL_DIS'
           when dollar_type.rowno = 3 then 'TTL_TAX'
       end dollar_name
      ,case
           when dollar_type.rowno = 1 then base_ttl_amt
           when dollar_type.rowno = 2 then base_ttl_dis
           when dollar_type.rowno = 3 then base_ttl_tax
       end base_amt
      ,case
           when dollar_type.rowno = 1 then original_ttl_amt
           when dollar_type.rowno = 2 then original_ttl_dis
           when dollar_type.rowno = 3 then original_ttl_tax
       end usd_amt
from (
      select
             terminal_id
            ,sale_date
            ,sum(case when currency_category_code = 'BASE'     then ttl_amt end) base_ttl_amt
            ,sum(case when currency_category_code = 'BASE'     then ttl_dis end) base_ttl_dis
            ,sum(case when currency_category_code = 'BASE'     then ttl_tax end) base_ttl_tax
            ,sum(case when currency_category_code = 'ORIGINAL' then ttl_amt end) original_ttl_amt
            ,sum(case when currency_category_code = 'ORIGINAL' then ttl_dis end) original_ttl_dis
            ,sum(case when currency_category_code = 'ORIGINAL' then ttl_tax end) original_ttl_tax
      from retail_sale
      group by
             terminal_id
            ,sale_date
     )
    ,(
      select rownum rowno
      from dual
      connect by level <= 3
     ) dollar_type
order by 1,2,3

How to Convert the Column To Row in Table

 STEP 1:-create table

create table new_colum_chane(
currency_category_code varchar2(8) -- base, original
,ttl_amt number
,ttl_dis number
,ttl_tax number)

STEP 2:-  Insert values


insert into new_colum_chane values ('ORIGINAL',100,20,4.80);

select * from new_colum_chane



STEP 3:- NOW TRY THIS QUERY

select currency_category_code,
            case
            when new_arg_insert.rowno = 1 then 'TTL_AMT'
            when new_arg_insert.rowno = 2 then 'TTL_DIS'
            when new_arg_insert.rowno = 3 then 'TTL_TAX'
                end dollar_name
                ,case
           when new_arg_insert.rowno = 1 then ttl_amt
           when new_arg_insert.rowno = 2 then ttl_dis
           when new_arg_insert.rowno = 3 then ttl_tax
       end amt
           from new_colum_chane
    ,(
      select rownum rowno
      from dual
      connect by level <=3
     ) new_arg_insert
order by 1,2


ANOTHER STEP :

select
max(sucess) sucess,
max( suc) suc
from
(
select
(case when ts='a' then va end) sucess,
(case when ts='b' then va end) suc
from
(
select
'a' ts,
'A' va
from dual
union all
select
'b' ts,
'B' va
from dual
))




 --  Thank you Sumathi --
 

To set ORG ID


begin
dbms_application_info.set_client_info(value);
end;


begin
fnd_client_info.set_org_context(value);
end;

P2P Query For All Stapes

P2P Queries

***********************************************************************************************************************************************************************************************************************
1. Requisition and PO

-----Relation with Requistion and PO
select r.segment1 "Req Num",
p.segment1 "PO Num"
from po_headers_all p,
po_distributions_all d,
po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
where p.po_header_id = d.po_header_id
and d.req_distribution_id = rd.distribution_id
and rd.requisition_line_id = rl.requisition_line_id
and rl.requisition_header_id = r.requisition_header_id

***********************************************************************************************************************************************************************************************************************

2. Internal Requisitions without Sales order


---used to list all Internal Requisitions that do not have an associated Internal Sales order
Select RQH.SEGMENT1 REQ_NUM,
RQL.LINE_NUM,
RQL.REQUISITION_HEADER_ID ,
RQL.REQUISITION_LINE_ID,
RQL.ITEM_ID ,
RQL.UNIT_MEAS_LOOKUP_CODE ,
RQL.UNIT_PRICE ,
RQL.QUANTITY ,
RQL.QUANTITY_CANCELLED,
RQL.QUANTITY_DELIVERED ,
RQL.CANCEL_FLAG ,
RQL.SOURCE_TYPE_CODE ,
RQL.SOURCE_ORGANIZATION_ID ,
RQL.DESTINATION_ORGANIZATION_ID,
RQH.TRANSFERRED_TO_OE_FLAG
from
PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
where
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
and RQL.SOURCE_TYPE_CODE = 'INVENTORY'
and RQL.SOURCE_ORGANIZATION_ID is not null
and not exists (select 'existing internal order'
from OE_ORDER_LINES_ALL LIN
where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

***********************************************************************************************************************************************************************************************************************

3. cancelled requisition

-----list My cancel Requistion
select prh.REQUISITION_HEADER_ID,
prh.PREPARER_ID ,
prh.SEGMENT1 "REQ NUM",
trunc(prh.CREATION_DATE),
prh.DESCRIPTION,
prh.NOTE_TO_AUTHORIZER
from apps.Po_Requisition_headers_all prh,
apps.po_action_history pah
where Action_code='CANCEL'
and pah.object_type_code='REQUISITION'
and pah.object_id=prh.REQUISITION_HEADER_ID


***********************************************************************************************************************************************************************************************************************

4. -----list all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO.
select
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date" ,
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
and pll.line_location_id(+) = prl.line_location_id
and pll.po_header_id = ph.po_header_id(+)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
AND PLL.LINE_LOCATION_ID IS NULL
AND PRL.CLOSED_CODE IS NULL
AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2


***********************************************************************************************************************************************************************************************************************

5. ----- List and all data entry from PR till PO

select distinct u.description "Requestor",
porh.segment1 as "Req Number",
trunc(porh.Creation_Date) "Created On",
pord.LAST_UPDATED_BY,
porh.Authorization_Status "Status",
porh.Description "Description",
poh.segment1 "PO Number",
trunc(poh.Creation_date) "PO Creation Date",
poh.AUTHORIZATION_STATUS "PO Status",
trunc(poh.Approved_Date) "Approved Date"
from apps.po_headers_all poh,
apps.po_distributions_all pod,
apps.po_req_distributions_all pord,
apps.po_requisition_lines_all porl,
apps.po_requisition_headers_all porh,
apps.fnd_user u
where porh.requisition_header_id = porl.requisition_header_id
and porl.requisition_line_id = pord.requisition_line_id
and pord.distribution_id = pod.req_distribution_id(+)
and pod.po_header_id = poh.po_header_id(+)
and porh.created_by = u.user_id
order by 2


***********************************************************************************************************************************************************************************************************************


6. -----list all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO.

select
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date" ,
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
and pll.line_location_id(+) = prl.line_location_id
and pll.po_header_id = ph.po_header_id(+)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
AND PLL.LINE_LOCATION_ID IS NULL
AND PRL.CLOSED_CODE IS NULL
AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2


***********************************************************************************************************************************************************************************************************************

7. ----- List and PO With there approval , invoice and payment details

select
a.org_id "ORG ID",
E.SEGMENT1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
f.ADDRESS_LINE1 "ADDRESS",
f.city "CITY",
f.country "COUNTRY",
to_char(trunc(d.CREATION_DATE)) "PO Date",
d.segment1 "PO NUM",
d.type_lookup_code "PO Type",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_id "ITEM ID" ,
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",
(select
decode(ph.approved_FLAG, 'Y', 'Approved')
from po.po_headers_all ph
where ph.po_header_ID = d.po_header_id)"PO Approved?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(select
decode(x.MATCH_STATUS_FLAG, 'A', 'Approved')
from ap.ap_invoice_distributions_all x
where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?",
a.amount_paid,
h.amount,
h.check_id,
h.invoice_payment_id "Payment Id",
i.check_number "Cheque Number",
to_char(trunc(i.check_DATE)) "PAYMENT DATE"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
where a.invoice_id = b.invoice_id
and b.po_distribution_id = c. po_distribution_id (+)
and c.po_header_id = d.po_header_id (+)
and e.vendor_id (+) = d.VENDOR_ID
and f.vendor_site_id (+) = d.vendor_site_id
and d.po_header_id = g.po_header_id
and c.po_line_id = g.po_line_id
and a.invoice_id = h.invoice_id
and h.check_id = i.check_id
and f.vendor_site_id = i.vendor_site_id
and c.PO_HEADER_ID is not null
and a.payment_status_flag = 'Y'
and d.type_lookup_code != 'BLANKET'

***********************************************************************************************************************************************************************************************************************

8. ---- List all open PO'S

select
h.segment1 "PO NUM",
h.authorization_status "STATUS",
l.line_num "SEQ NUM",
ll.line_location_id,
d.po_distribution_id ,
h.type_lookup_code "TYPE"
from
po.po_headers_all h,
po.po_lines_all l,
po.po_line_locations_all ll,
po.po_distributions_all d
where h.po_header_id = l.po_header_id
and ll.po_line_id = l.po_Line_id
and ll.line_location_id = d.line_location_id
and h.closed_date is null
and h.type_lookup_code not in ('QUOTATION') ;

and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'.

***********************************************************************************************************************************************************************************************************************

9. Relation between Requisition and PO tables

Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

***********************************************************************************************************************************************************************************************************************


10.You need to find table which hold PO Approval path...

These two table keeps the data:

PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES


***********************************************************************************************************************************************************************************************************************


11.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..

The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.

The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.

These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is
Purchasing.

Budgetary Records from PO (These include reservations, reversals and cancellations):

REFERENCE_1- Source (PO or REQ)
REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
po_requisition_headers_all.requisition_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or
po_req_distributions_all.distribution_id)
REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or
po_requisition_headers_all.segment1)
REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:

REFERENCE_1- Source (PO)
REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.


***********************************************************************************************************************************************************************************************************************


12. A standard Quotations one that you can tie back to a PO.

Navigate to RFQ -> Auto create -> enter a PO and reference it back.

***********************************************************************************************************************************************************************************************************************


13. I want to debug for a PO , where should I start.

Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.



Stage 1: PO Creation :

PO_HEADERS_ALL

select po_header_id from po_headers_all where segment1 =;

select * from po_headers_all where po_header_id =;

po_lines_all
select * from po_lines_all where po_header_id =;

po_line_locations_all

select * from po_line_locations_all where po_header_id =;

po_distributions_all

select * from po_distributions_all where po_header_id =;

po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =;


Stage 2: Once PO is received data is moved to respective receving tables and inventory tables

RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =);

RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =;

RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =;

RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =);

RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =;

MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =);

AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =));

Stage 4 : Many Time there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id = );

Stage 5 : General Ledger

Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN ('');

GL_INTERFACE

SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id ));

GL_IMPORT_REFERENCES

SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =))

Monday 27 June 2011

how to find the alternative rows in sql

SELECT *
FROM   emp
WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)
                     FROM   emp);



SELECT *
FROM   emp
WHERE  (ROWID,0) NOT  IN (SELECT ROWID, MOD(ROWNUM,2)
                     FROM   emp);

TO Find The Onhand_quantity_in_particular_day

select  ITEM_ID,
   sum(TARGET_QTY) quantity
   from (
     SELECT 
              moqd.inventory_item_id item_id,            
              SUM (primary_transaction_quantity) target_qty
       FROM   mtl_onhand_quantities_detail moqd
      WHERE   moqd.organization_id = organizations
              AND moqd.owning_tp_type = DECODE (2, 2, 2, moqd.owning_tp_type)
   GROUP BY  moqd.inventory_item_id
   UNION
     SELECT
              mmt.inventory_item_id item_id,
              -SUM (primary_quantity) target_qty
       FROM   mtl_material_transactions mmt,
              mtl_txn_source_types mtst,
              mtl_parameters mp
      WHERE   mmt.organization_id = organizations AND mp.organization_id = organizations
                                                                    AND transaction_date >= (start_date + 1)
              AND NVL (mmt.owning_tp_type, 2) =
                    DECODE (2, 2, 2, NVL (mmt.owning_tp_type, 2))
              AND mmt.transaction_source_type_id =
                    mtst.transaction_source_type_id
              AND NVL (mmt.logical_transaction, 2) <> 1
   GROUP BY  
              mmt.inventory_item_id
              )
              group by  ITEM_ID

Remove the string Character and multeple the number

create or replace function  bom_multi_drill_down  (v_input_temp varchar2  )  return number is 
   v_input  varchar2(200);
   v_output   number;
   v_dummy    number;
   v_loop     VARCHAR2 (100);
  
  
BEGIN

v_input := v_input_temp;

v_output := 1;

   WHILE TRUE
   LOOP
      v_dummy := to_number(SUBSTR (v_input, 1, INSTR (v_input, ',') - 1));         
      IF v_dummy IS NULL
      THEN
         v_output := v_output ;
      ELSE
         v_output := (v_output * v_dummy) ;
         
        
      END IF;

      v_input := SUBSTR (v_input, INSTR (v_input, ',') + 1);

      IF INSTR (v_input, ',') = 0
      THEN
         IF v_input IS NULL
         THEN
            v_output := v_output ;
         ELSE
            v_output := (v_output * v_input);
         END IF;

         v_input := NULL;
      END IF;

 

      IF v_input IS NULL
      THEN
         EXIT;
      END IF;
   END LOOP;
  
   DBMS_OUTPUT.put_line ('OUTPUT : ' || v_output);
  
   return(v_output);
  
END bom_multi_drill_down ;

To Find in which "Request group " the Particular Concurrent is Registered

SELECT EXECUTABLE_NAME
     ,EXECUTION_FILE_NAME
     ,APPLICATION_SHORT_NAME
     , fcpl.user_concurrent_program_
name
     , fcp.concurrent_program_name
     , par.end_user_column_name
     , par.form_left_prompt prompt
     , par.enabled_flag
     , par.required_flag
     , par.display_flag
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl par
     , FND_EXECUTABLES fe
     ,fnd_application fa
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name ='India - Debtors Trial Balance Report'
AND    fcpl.LANGUAGE = 'US'
AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
and   fe.application_id=fa.application_id
and   fe.EXECUTABLE_ID = fcp.executable_id

Deleting a concurrent program from back end

Begin

fnd_program.delete_program('program short name','schema');


fnd_program.delete_executable('program short name','schema');


commit;


End;

ITEM INTERFACE PROCEDURE & DATA &CONTORL FILE

DATA FILE:


AVK,204,VIMAL,CREATE,1,1001,@Finished Good,FG,V1

BVK,204,VINOT,CREATE,1,1001,@Finished Good,FG,V1

MRB,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

RAK,204,ARAVI,CREATE,1,1001,@Finished Good,FG,V1

BVP,204,VIVIN,CREATE,1,1001,@Finished Good,FG,V1

ARK,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

MBM,204,BALAS,CREATE,1,1001,@Finished Good,FG,V1

MMS,204,SAMIS,CREATE,1,1001,@Finished Good,FG,V1

SJM,204,JOTHI,CREATE,1,1001,@Finished Good,FG,V1

RKN,204,KANNA,CREATE,1,1001,@Finished Good,FG,V1



CONTROL FILE:


LOAD DATA

INFILE '/oracle2/VIS1211/apps/apps_st/appl/inv/12.0.0/bin/oit.txt'

INSERT INTO TABLE iteminterface

FIELDS TERMINATED BY ','

(

SEGMENT1 ,

ORGANIZATION_ID,

DESCRIPTION,

TRANSACTION_TYPE,

PROCESS_FLAG,

SET_PROCESS_ID,

template_name,

item_type,

organization_code

)


Procedure Program:


CREATE OR REPLACE

PROCEDURE APPS.vimal_interface

(

ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2)

IS

LN_REC_CNT NUMBER := 0;

v_template_name NUMBER;

v_organization NUMBER;

l_error_details VARCHAR2 (4000) := NULL;

l_error_flag VARCHAR2 (2000) := NULL;

l_failure_count NUMBER := 0;

l_success_count NUMBER := 0;

l_user_id NUMBER := fnd_global.user_id;

l_resp_id NUMBER := fnd_global.resp_id;

l_appl_id NUMBER := fnd_global.resp_appl_id;

CURSOR DATA_LOAD

IS

SELECT * FROM iteminterface;

BEGIN

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR I IN DATA_LOAD

LOOP

l_error_flag := NULL;

l_error_details := NULL;

LN_REC_CNT := LN_REC_CNT + 1;

BEGIN

SELECT COUNT (*)

INTO v_organization

FROM org_organization_definitions

WHERE organization_code = i.organization_code;

IF v_organization = 0 THEN

l_error_flag := 'Y';

l_error_details := l_error_details || '\' || 'Organization Error' || '\' || SUBSTR (SQLERRM, 1, 100);

END IF;

END;

BEGIN

SELECT COUNT (*)

INTO v_template_name

FROM mtl_item_templates

WHERE template_name = i.template_name;

IF v_template_name = 0 THEN

l_error_flag := 'Y';

l_error_details := l_error_details || '\' || 'Template_Name Error' || '\' || SUBSTR (SQLERRM, 1, 100);

END IF;

END;

IF (l_error_flag = 'Y') THEN

l_failure_count := l_failure_count + 1;

ELSE

INSERT

INTO MTL_SYSTEM_ITEMS_INTERFACE

(

SEGMENT1 ,

ORGANIZATION_ID ,

DESCRIPTION ,

TRANSACTION_TYPE,

PROCESS_FLAG ,

SET_PROCESS_ID ,

template_name ,

item_type ,

organization_code

)

VALUES

(

i.SEGMENT1 ,

i.ORGANIZATION_ID ,

i.DESCRIPTION ,

i.TRANSACTION_TYPE,

i.PROCESS_FLAG ,

i.SET_PROCESS_ID ,

i.template_name ,

i.item_type ,

i.organization_code

);

l_success_count := l_success_count + 1;

END IF;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

FND_FILE.PUT_LINE

(

FND_FILE.LOG,'Records not loaded into the interface table'

)

;

END;

ITEM INTERFACE STEPS


ITEM INTERFACE
----------------------------

1.Create a Data File.

2.Create a Control File

3.Create a Stagging Tables

4.Move Data&Control File in gftp in This directory
/oracle2/VIS1211/apps/apps_st/appl/inv/12.0.0/bin

5.In Apps

Switch Responsibility->Application Developer->Concurrent->Executable

Executable :oit865
Short Name :oit865
Application :Inventory
Execution Method :SQL Loader
Execution File Name :oit865

Switch Responsibility->Application Developer->Concurrent->Program

Program :oit865
Short Name :oit865
Application :Inventory
Name :oit865



Switch Responsibility->System Administrator

Security->Responsibility->Request

Group :All Inclusive GUI
Application :Inventory

Give the Control File Name and save it.


Switch Responsibility->inventory,vision operations(USA)

View->Request->submit a new request


Name :oit865

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are loaded into stagging table

Table Name:iteminterface

After Finishing the Stagging table move the table to interface table using PL/SQL Procedure.

Switch Responsibility->Application Developer->Concurrent->Executable

Executable :vimal_interface
Short Name :vimal_interface
Application :Inventory
Execution Method :PL/SQL Stored Procedure
Execution File Name :vimal_interface


Switch Responsibility->Application Developer->Concurrent->Program

Program :vimal_interface
Short Name :vimal_interface
Application :Inventory
Name :vimal_interface


Switch Responsibility->System Administrator

Security->Responsibility->Request

Group :All Inclusive GUI
Application :Inventory

Give the interface table name and save it.

Switch Responsibility->inventory,vision operations(USA)

view->request->submit a new request


Name :vimal_interface

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are moved from stagging table to interface table.

Table Name:mtl_system_items_interface

Then we want to move from interface table to base table

Inventory Vision Operations->View->Request


Name :Import Items

click submit

Then the Program Will Compile and Run Normal.
mtl_system_items_interface
Then the Data's are moved from interface table to base table.

Table Name:select * from mtl_system_items_b order by creation_date desc;

mtl_system_items_interface


 


HRMS API'S LIST_1

Element Loading
py_element_entry_api.create_element_entry
py_element_entry_api.update_element_entry
Load Salaries
hr_upload_proposal_api.upload_salary_proposal
Approve Salary Proposal
hr_maintain_proposal_api.approve_salary_proposal
Starts To Validate/Load Federal Tax For A Person pay_federal_tax_rule_api.update_fed_tax_rule
State tax rules
pay_state_tax_rule_api.create_state_tax_rule
pay_state_tax_rule_api.update_state_tax_rule
County Tax Rules
pay_county_tax_rule_api.create_county_tax_rule
pay_county_tax_rule_api.update_county_tax_rule
City Tax Rules
pay_city_tax_rule_api.create_city_tax_rule
pay_city_tax_rule_api.update_city_tax_rule
Schools and Colleges
per_esa_upd.upd
per_esa_ins.ins
Performance Reviews
hr_perf_review_api.create_perf_review
hr_perf_review_api.update_perf_review
State Information Taxes
hr_sit_api.update_sit
hr_sit_api.create_sit
Qualifications
per_qualifications_api.create_qualification
per_qualifications_api.update_qualification
Locations
hr_location_api.update_location
hr_location_api.create_location
Organization
hr_organization_api.update_organization
hr_organization_api.create_org_classification
If any Change in Organization information.
if information2 = 'Y' then
hr_organization_api.enable_org_classification
If any Change in Organization information.
if information2 = 'N' then
hr_organization_api.disable_org_classification
If Organization does not exist in instance
hr_organization_api.create_organization api
Jobs
hr_job_api.update_job
hr_job_api.create_job
Positions
hr_position_api.update_position
hr_position_api.create_position