Query to get Goods Return Not Invoiced(GRNI)

 SELECT

        fnd_flex_ext.get_segs('GL','GL#',glc2.chart_of_accounts_id,glc2.code_combination_id) accrual_account                                                                                                                     ,

        fun.bu_name                                                                          operating_unit                                                                                                                      ,

        poh.segment1                                                                         po_number                                                                                                                           ,

        pol.line_num                                                                         po_line_number                                                                                                                      ,

        poh.currency_code                                                                    po_currency_code                                                                                                                    ,

        pol.matching_basis                                                                                                                                                                                                       ,

        rcvtr.quantity_received                                                                                                                                                                                                  ,

        fnd_flex_ext.get_segs('GL','GL#',glc1.chart_of_accounts_id,glc1.CODE_COMBINATION_ID)                                                                                                         Expense_Account             ,

        TRUNC(SYSDATE)-TRUNC(poh.creation_date)                                                                                                                                                      AS aged_in_days             ,

        ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',pol.amount,pol.unit_price*pol.quantity),0),2)                                                                                                      Entered_po_amount       ,

        NVL(invn.amount_billed ,0)                                                                                                                                                                      Entered_ap_amount       ,

        ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received, pol.unit_price*rcvtr.quantity_received),0),2)                                                                               Entered_GRN_amount  ,

NVL(invn.amount_billed ,0) - NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received,pol.unit_price*rcvtr.quantity_received),0)                                                            Entered_accrued_balance,

        ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',pol.amount*NVL(poh.rate,1),pol.unit_price*pol.quantity*NVL(poh.rate,1)),0),2)                                                                      Functional_po_amount              ,

        NVL(amount_billed_b,0)                                                                                                                                                                          Functional_ap_amount,

        ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received*NVL(poh.rate,1),pol.unit_price*rcvtr.quantity_received*NVL(poh.rate,1)),0),2)                                                Functional_GRN_amount,         

        NVL(amount_billed_b,0) - ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received*NVL(poh.rate,1),pol.unit_price*rcvtr.quantity_received*NVL(poh.rate,1)),0),2)                       Functional_accrued_balance, 

        psv.vendor_name                                                                                                                                                                                 supplier_name            ,

        psv.segment1                                                                                                                                                                                    supplier_number          ,

        pssa.vendor_site_code                                                                                                                                                                           supplier_site_name       ,

        (

                SELECT

                        per.full_name

                FROM

                        por_requisition_lines_all req,

                        per_person_names_f        per

                WHERE

                        pol.po_line_id   = req.po_line_id

                AND     req.requester_id = per.person_id

                AND     rownum          <= 1 ) AS requisitioner,

        (

                SELECT

                        ppf.full_name

                FROM

                        per_person_names_f ppf

                WHERE

                        ppf.person_id = poh.agent_id

                AND     ppf.name_type ='GLOBAL'

                AND     TRUNC(SYSDATE) BETWEEN TRUNC(ppf.effective_start_date)AND     TRUNC(ppf.effective_end_date)) buyer

FROM

        po_headers_all poh

,poz_suppliers_v psv

,poz_supplier_sites_v pssa

,poz_suppliers_pii supps_pii

,fun_all_business_units_v fun

,po_lines_all pol

,po_line_locations_all poll

,hr_organization_units_f_tl hro

,(

                SELECT DISTINCT

                        po_line_id             ,

                        line_location_id       ,

                        pjc_project_id         ,

                        pjc_task_id            ,

                        pjc_organization_id    ,

                        pjc_expenditure_type_id,

                        code_combination_id    ,

                        accrual_account_id

                FROM

                        po_distributions_all) pod

,pjf_projects_all_vl prj

,hr_locations_all dloc

,pjf_tasks_v task

,pjf_exp_types_vl exp

,pjf_organizations_expend_v exp_org

,(

                SELECT

                        rsl.po_line_id                                                                                                              ,

                        rsl.po_line_location_id                                                                                                     ,

                        SUM( DECODE( rt.transaction_type, 'RETURN TO VENDOR', NVL(rt.quantity, 0) * -1, NVL(rt.quantity, 0) ) ) AS quantity_received,

                        SUM( DECODE( rt.transaction_type, 'RETURN TO VENDOR', NVL(rt.amount, 0)   * -1, NVL(rt.amount, 0) ) )   AS amount_received

                FROM

                        rcv_transactions     rt ,

                        rcv_shipment_headers rsh,

                        rcv_shipment_lines   rsl

                WHERE

                        rsh.shipment_header_id   = rt.shipment_header_id

                AND     rsl.shipment_header_id   = rsh.shipment_header_id

                AND     rsl.shipment_line_id     = rt.shipment_line_id

                AND     rt.destination_type_code = 'RECEIVING'

                GROUP BY

                        rsl.PO_LINE_ID,

                        rsl.PO_LINE_LOCATION_ID ) rcvtr

,GL_CODE_COMBINATIONS glc1

,GL_CODE_COMBINATIONS glc2

,(

                SELECT

                        l.po_line_id                                 ,

                        po_line_location_id                          ,

                        COUNT(DISTINCT l.invoice_id)      AS num_invoices ,

                        SUM(l.amount)                     AS amount_billed,

                        SUM(NVL(l.base_amount, l.amount)) AS amount_billed_b

                FROM

                        ap_invoice_lines_all l,

                        ap_invoices_all      h

                WHERE

                        h.invoice_id = l.invoice_id

                GROUP BY

                        po_line_id,

                        po_line_location_id ) invn

,xle_entity_profiles xle

,egp_categories_vl cat

WHERE 1=1

AND poh.vendor_id = psv.vendor_id

AND poh.vendor_site_id = pssa.vendor_site_id

AND supps_pii.vendor_id = psv.vendor_id

AND poh.billto_bu_id = fun.bu_id

AND poh.po_header_id = pol.po_header_id

AND pol.po_line_id = poll.po_line_id

AND poll.ship_to_organization_id = hro.organization_id

AND hro.language                 = userenv('LANG')

AND pol.po_line_id        = pod.po_line_id

AND poll.line_location_id = pod.line_location_id

AND prj.project_id = pod.pjc_project_id(+)

AND dloc.location_id=poll.ship_to_location_id(+)

AND task.task_id = pod.pjc_task_id(+)

AND exp.expenditure_type_id = pod.pjc_expenditure_type_id(+)

AND exp_org.organization_id = pod.pjc_organization_id(+)

AND exp_org.org_id          =fun.bu_id(+)

AND pol.PO_LINE_ID        = rcvtr.PO_LINE_ID

AND poll.LINE_LOCATION_ID = rcvtr.PO_LINE_LOCATION_ID

AND pod.CODE_COMBINATION_ID = glc1.CODE_COMBINATION_ID(+)

AND pod.ACCRUAL_ACCOUNT_ID = glc2.CODE_COMBINATION_ID(+)

AND poll.po_line_id       = invn.po_line_id(+)

AND poll.line_location_id = invn.po_line_location_id(+)

AND poh.soldto_le_id = xle.legal_entity_id

AND cat.category_id = pol.category_id(+)

AND NVL(invn.amount_billed ,0) <> NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received, pol.unit_price*rcvtr.quantity_received),0)

AND (fun.bu_id IN (:p_bu_id) OR COALESCE(:p_bu_id, NULL) IS NULL)

AND (psv.vendor_id IN (:p_vendor_id) OR COALESCE(:p_vendor_id, NULL) IS NULL)

ORDER BY

        fun.bu_name,

        poh.segment1,

        pol.line_num

Query to get Negotiation , Bid , Requestions (Bid Analysis)

 -- Bid Analysis Details

with lookup_values as ( 

    select lookup_type, 

           lookup_code, 

           meaning, 

           view_application_id

    from   fnd_lookup_values_vl flvl

    where  flvl.lookup_type in ('ZX_TRL_TRANSFERRED_STATUS','PON_AWARD_STATUS','YES_NO')

    and    flvl.enabled_flag='Y'

    and    sysdate between nvl(flvl.start_date_active,to_date('01/01/1951','DD/MM/YYYY')) and nvl(flvl.end_date_active,to_date('31/12/4712','DD/MM/YYYY')))

SELECT 

fabu.bu_name business_unit,

paha.document_number negotiation_number,

paha.auction_title negotiation_title,

padb.negotiation_display_name negotiation_type,

-- (select flv.meaning

-- from  fnd_lookups flv

-- where flv.lookup_type = 'PON_AUCTION_STATUS'

-- and paha.auction_status = flv.lookup_code

-- ) Negotiation_Status,

paha.auction_status Negotiation_Status,

--(select style_name from pon_negotiation_styles_tl where style_id=paha.style_id and LANGUAGE = 'US' )as negotiation_style,

pnst.style_name as negotiation_style,

paha.currency_code Negotiation_currency,

--TO_CHAR(paha.creation_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Negotiation_Creation_Date,

paha.creation_date as Negotiation_Creation_Date,

--TO_CHAR(paha.open_bidding_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Negotiation_Open_Date,

paha.open_bidding_date as Negotiation_Open_Date,

--TO_CHAR(paha.close_bidding_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') Negotiation_Closed_Date,

paha.close_bidding_date as Negotiation_Closed_Date,

abs(paha.close_bidding_date - sysdate) Negotiation_Time_remaining,

(SELECT DISPLAY_NAME 

FROM per_person_names_f_v  

WHERE person_id = pha.agent_id

AND name_type='GLOBAL'

AND SYSDATE BETWEEN effective_start_date AND effective_end_date)Buyer_Name,

pbh.bid_number,

ps.vendor_name supplier,

pssv.vendor_site_code Supplier_Site,

(SELECT pscv.party_name 

  FROM poz_supplier_contacts_v pscv 

 WHERE 1=1

 AND pha.vendor_contact_id =pscv.vendor_contact_id

 AND ps.vendor_id = pscv.vendor_id

)supplier_contact,

paha.MAX_BID_COLOR_SEQUENCE_ID responses,

(select meaning 

 from   lookup_values 

 where  lookup_type='ZX_TRL_TRANSFERRED_STATUS'

 and    lookup_code=pbh.poq_transfer_status) response_status,

paip.target_price,

paip.requisition_number,

paha.requested_by requestor,

prla.amount requisition_line_amount,

pbh.overall_rank,

--TO_CHAR(pbav.date_value, 'DD-MON-YYYY hh24:mi','NLS_DATE_LANGUAGE=AMERICAN') Date_of_Response,

pbav.date_value as Date_of_Response,

pbav.created_by response_entered_by,

pbh.submit_stage negotiation_stage,

(select meaning 

 from   lookup_values 

 where  lookup_type='YES_NO'

 and    view_application_id=0

 and    lookup_code=paip.no_award) award,

(select meaning 

 from   lookup_values 

 where  lookup_type='PON_AWARD_STATUS'

 and    lookup_code=paip.award_status ) award_status,

null award_status_description,

--TO_CHAR(paha.award_complete_date, 'DD-MON-YYYY hh24:mi','NLS_DATE_LANGUAGE=AMERICAN') award_completion_date,

paha.award_complete_date as award_completion_date,

--TO_CHAR(paha.award_approval_date, 'DD-MON-YYYY hh24:mi','NLS_DATE_LANGUAGE=AMERICAN') award_approved_date,

paha.award_approval_date as award_approved_date,

pha.segment1 purchase_order,

pha.document_status purchase_order_status,

paha.total_savings_amount Savings,

((paha.approval_amount-paha.total_savings_amount)*100/paha.total_savings_amount) savings_percentage,

ppa.segment1 project_number,

(SELECT task_number FROM pjf_tasks_v ptv WHERE ptv.task_id = pda.pjc_task_id)task_number,

(SELECT task_name FROM pjf_tasks_v ptv WHERE ptv.task_id = pda.pjc_task_id)task_name,

abs(TRUNC(paha.award_approval_date) - TRUNC(paha.award_complete_date)) negotiation_award_cycle_days,

abs(TRUNC(paha.open_bidding_date) - TRUNC(paha.close_bidding_date))  negotiation_bidding_cycle_days,

ABS(trunc(paha.publish_date) - trunc(paha.close_bidding_date)) as Negotiation_publication_to_completion_dates,

ABS(TRUNC(paha.open_bidding_date)-TRUNC(paha.close_bidding_date))as negotiation_bidding_cycle

FROM 

pon_bid_headers              pbh,

pon_auction_headers_all      paha,

pon_auction_item_prices      paip,

poz_suppliers_v              ps,

poz_supplier_sites_v         pssv,

po_headers_all               pha,

po_distributions_all         pda,

por_req_distributions_all     prda,

por_requisition_lines_all     prla,

por_requisition_headers_all   prha,

pon_bid_po_numbers           pnpo,

pjf_projects_all_vl          ppa,

fun_all_business_units_v     fabu,

PON_DOCTYPE_STYLES_VL padb,

pon_bid_attribute_values     pbav,

pon_negotiation_styles_tl pnst

WHERE 1=1

AND pnst.style_id=paha.style_id

AND pnst.LANGUAGE = 'US'

--AND paha.document_number='ITNEG10030,1'

AND pbh.bid_status <> 'DRAFT'

AND paha.auction_header_id = pbh.auction_header_id

AND paha.auction_header_id = paip.auction_header_id

AND ps.vendor_id = pbh.vendor_id

AND pssv.vendor_site_id = pbh.vendor_site_id

AND pha.po_header_id = pda.po_header_id(+)

AND pda.req_distribution_id = prda.distribution_id(+)

AND prda.requisition_line_id = prla.requisition_line_id(+)

AND prla.requisition_header_id = prha.requisition_header_id(+)

AND pnpo.po_header_id = pha.po_header_id(+)

AND pnpo.auction_header_id(+) = pbh.auction_header_id

AND pda.pjc_project_id = ppa.project_id(+)

AND paha.PRC_BU_ID = fabu.bu_id

AND padb.doctype_id = paha.doctype_id

AND PADB.negotiation_display_name ='Auction'

--AND padb.LANGUAGE='US'

AND PADB.enabled_flag='Y'

AND paha.STYLE_ID=padb.STYLE_ID

AND paha.auction_header_id = pbav.auction_header_id(+)

AND (fabu.bu_id IN (:p_bu_id) OR COALESCE(:p_bu_id, NULL) IS NULL)--

AND (paha.auction_header_id IN (:p_auction_header_id) OR COALESCE(:p_auction_header_id, NULL) IS NULL)--

--AND (padb.doctype_id IN (:p_negotiation_type) OR COALESCE(:p_negotiation_type, NULL) IS NULL)--

AND (ps.vendor_id IN (:p_vendor_id) OR COALESCE(:p_vendor_id, NULL) IS NULL)--

AND (ppa.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)--

AND (pha.agent_id IN (:p_buyer_id) OR COALESCE(:p_buyer_id, NULL) IS NULL)--

AND (prha.requisition_header_id  IN (:p_requisition_header_id) OR COALESCE(:p_requisition_header_id, NULL) IS NULL)

AND (paha.requested_by IN (:p_requestor) OR COALESCE(:p_requestor, NULL) IS NULL)

AND (pbh.bid_number IN (:p_bid_num) OR COALESCE(:p_bid_num, NULL) IS NULL)

AND (paha.auction_status IN (:p_neg_status) OR COALESCE(:p_neg_status, NULL) IS NULL)

AND (paha.award_status IN (:p_award_status) OR COALESCE(:p_award_status, NULL) IS NULL)

AND TRUNC(paha.award_complete_date) BETWEEN NVL(:p_dt_from,paha.award_complete_date) AND NVL(:p_dt_to,paha.award_complete_date)

Query to get Requestion Approval rules (DOA)

 with por_lookup_list as ( 

    select lookup_type, 

           lookup_code, 

           meaning

    from   fnd_lookups 

    where  enabled_flag='Y'

    and    sysdate between nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) and nvl(end_date_active,to_date('31/12/4712','DD/MM/YYYY'))

    and    lookup_type in ('POR_AMX_ACTION','POR_AMX_ROUTE_TO','POR_AMX_SINGLE_APPROVER','YES_NO'))

select tl.meaning TASK,

       r.rule_id, 

       r.display_rule_name RULE,

      (select meaning 

       from   por_lookup_list

       where  r.active_flag=lookup_code

       and    lookup_type ='YES_NO') isactive,

       sl.meaning STAGE, 

       decode(ALWAYS_APPLY_FLAG,'Y','Always Apply', r.conditions_string) CONDITION,

       pl.meaning PARTICIPANT,

      (select meaning 

       from   por_lookup_list

       where  a.action_code=lookup_code

       and    lookup_type ='POR_AMX_ACTION')action_type,

      (select meaning 

       from   por_lookup_list

       where  a.ROUTE_USING_CODE=lookup_code

       and    lookup_type ='POR_AMX_ROUTE_TO' ) Route_Using,

      (select meaning 

       from   por_lookup_list

       where  a.user_type_code=lookup_code

       and    lookup_type ='POR_AMX_SINGLE_APPROVER' )User_Type,

       paag.APPROVAL_GROUP_NAME Approval_Group,

      (select listagg(fwags.member,',') as mem

       from   fa_fusion_soainfra.wfapprovalgroups  fwag,

              fa_fusion_soainfra.wfapprovalgroupmembers fwags

       where  fwag.approvalgroupid=fwags.approvalgroupid

       and    fwag.approvalgroupname=paag.approval_group_name

       group by fwag.approvalgroupid,

                fwag.approvalgroupname) members

from   por_amx_rules r, 

       por_amx_tasks t,

       fnd_lookups tl, 

       por_amx_participants p, 

       fnd_lookups pl, 

       por_amx_stages s, 

       fnd_lookups sl,

       por_amx_actions a,

       por_amx_approval_groups paag

where  1=1

and   r.sandbox_flag='Y' 

and   r.task_id=t.task_id

and   tl.lookup_type=t.task_lookup 

and   tl.lookup_code=t.lookup_code 

and   r.participant_id=p.participant_id

and   t.participant_lookup=pl.lookup_type 

and   pl.lookup_code=p.lookup_code 

and   s.stage_id=p.stage_id

and   t.stage_lookup=sl.lookup_type

and   s.lookup_code=sl.lookup_code

and   a.rule_id=r.rule_id

and   r.active_flag ='Y'

and   a.approval_group_name = paag.APPROVAL_GROUP_NAME

--and   paag.APPROVAL_GROUP_NAME='DOA_ITA_L2_03'

--and   t.task_key=:bindTaskKey 

and  (t.task_lookup in (:p_task_name) or coalesce(:p_task_name, null) is null)

and  (r.active_flag in (:p_active) or coalesce(:p_active, null) is null)

and   r.creation_date between nvl(:p_dt_from,r.creation_date) and nvl(:p_dt_to,r.creation_date)

order by sl.meaning,

         pl.meaning desc

Query to get Negotiation , Bid and RFQ Details (RFQ Details Buyer )

 -- RFQ DETAILS

WITH LOOKUP_VALUES AS ( 

    SELECT LOOKUP_TYPE, 

           LOOKUP_CODE, 

           MEANING,

           VIEW_APPLICATION_ID

    FROM   FND_LOOKUP_VALUES_VL FLVL

    WHERE  FLVL.LOOKUP_TYPE IN ('PON_AUCTION_STATUS','PON_AWARD_STATUS','YES_NO','PON_CONTRACT_TYPE')

    AND    FLVL.ENABLED_FLAG='Y'

    AND    SYSDATE BETWEEN NVL(FLVL.START_DATE_ACTIVE,TO_DATE('01/01/1951','DD/MM/YYYY')) AND NVL(FLVL.END_DATE_ACTIVE,TO_DATE('31/12/4712','DD/MM/YYYY')))

SELECT 

      BUSINESS_UNIT,

  RFQ_NUMBER,

  NEGOTIATION_TITLE,

  NEGOTIATION_SYNOPSIS,

  NEGOTIATION_TYPE,

  NEGOTIATION_STYLE,

      NEGOTIATION_STATUS,

      NEGOTIATION_CURRENCY,

  OUTCOME_DOCUMENT_TYPE,

      NEGOTIATION_CREATION_DATE,

  NEGOTIATION_PREVIEW_DATE,

  NEGOTIATION_OPEN_DATE,

  TIME_REMAINING,

      NEGOTIATION_CLOSE_DATE ,

  BUYER, 

      AWARD,

      AWARD_DATE,

      AWARD_NUMBER,

      AWARD_STATUS,

      AWARD_APPROVED_DATE,

  AWARDED_SUPPLIER,

  PROJECT_NUMBER,

  PROJECT_NAME,

  PROJECT_MANAGER,

  RELATED_REQUISITION_NUMBER,   

  ISSUED_PO_NUMBER,  

      COUNT(TOTAL_NO_OF_BIDS) AS TOTAL_NO_OF_BIDS,   

  COUNT(NUMBER_OF_AWARDS) NUMBER_OF_AWARDS,

  COUNT(TOTAL_ISSUED_RFQ_SUPPLIERS) AS TOTAL_ISSUED_RFQ_SUPPLIERS,

  COUNT(TOTAL_ISSUED_POS) AS TOTAL_ISSUED_POS ,

  COUNT(TOTAL_NUMBER_OF_SUPPLIERS_INVITED) AS TOTAL_NUMBER_OF_SUPPLIERS_INVITED,

      COUNT(TOTAL_NUMBER_OF_SUPPLIERS_ACKNOWLEDGED) AS TOTAL_NUMBER_OF_SUPPLIERS_ACKNOWLEDGED,

  NEGOTIATION_PUBLISH_DATE,

  NEGOTIATION_CYCLE_TIME,

  NEGOTIATION_AWARDCYCLE_TIME,

  NEGOTIATION_BIDDINGCYCLE_TIME,

  NEGOTIATION_PUBLICATION_TO_COMPLETION_DAYS,

  BUYER_ENTITY,

  REQUESTER

  --SUPPLIER

  

  

FROM 

( 

SELECT DISTINCT

FBU.BU_NAME                                                                                     AS  BUSINESS_UNIT,

PAH.DOCUMENT_NUMBER                                                                             AS  RFQ_NUMBER,

PAH.AUCTION_TITLE                                                                               AS  NEGOTIATION_TITLE,

PAH.SYNOPSIS                                                                                    AS  NEGOTIATION_SYNOPSIS,

PADV.NAME                                                                                       AS NEGOTIATION_TYPE,

PNSV.STYLE_NAME                                                                                 AS NEGOTIATION_STYLE,

PAH.AUCTION_STATUS   AS NEGOTIATION_STATUS, -- COULDN'T FIND LOOKUP ENTRY FOR THIS... 

PAH.CURRENCY_CODE                                                                               AS NEGOTIATION_CURRENCY,

(SELECT MEANING 

 FROM   LOOKUP_VALUES 

 WHERE  LOOKUP_TYPE='PON_CONTRACT_TYPE'

 AND    LOOKUP_CODE=PAH.OUTCOME_STATUS ) AS OUTCOME_DOCUMENT_TYPE,

PAH.CREATION_DATE                                                                               AS NEGOTIATION_CREATION_DATE,

PAH.VIEW_BY_DATE                                                                                AS NEGOTIATION_PREVIEW_DATE,

PAH.OPEN_BIDDING_DATE                                                                           AS NEGOTIATION_OPEN_DATE,    

 ABS(TRUNC(PAH.CLOSE_DATE_PAUSE_ADJUSTED)-TRUNC(SYSDATE))  AS TIME_REMAINING,    

PAH.CLOSE_DATE_PAUSE_ADJUSTED                                                                          AS      NEGOTIATION_CLOSE_DATE,

(SELECT DISTINCT PPNF.DISPLAY_NAME     

FROM PER_PERSON_NAMES_F PPNF,

PON_BUYER_ACTIVITIES PBA

WHERE 

PBA.AUCTION_HEADER_ID = PAH.AUCTION_HEADER_ID

AND PBA.ACTIVITY_OWNER_ID = PPNF.PERSON_ID

AND PPNF.NAME_TYPE='GLOBAL'

AND PBA.ACTIVITY_CODE = 'NEW_COLLAB_TEAM_ASSIGNMENT'

AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE)                                              AS BUYER, 

(SELECT MEANING 

 FROM   LOOKUP_VALUES 

 WHERE  LOOKUP_TYPE='YES_NO'

 AND    VIEW_APPLICATION_ID=0

 AND    LOOKUP_CODE=PAIP.NO_AWARD ) AS AWARD,

PAH.AWARD_DATE                                                                                 AS   AWARD_DATE,

PAH.SOURCE_DOC_NUMBER                                                                                       AS       AWARD_NUMBER,

(SELECT MEANING 

 FROM   LOOKUP_VALUES 

 WHERE  LOOKUP_TYPE='PON_AWARD_STATUS'

 AND    LOOKUP_CODE=PAH.AWARD_STATUS ) AS AWARD_STATUS,

PAH.AWARD_APPROVAL_DATE                                                                                    AS             AWARD_APPROVED_DATE, 

CASE WHEN PAH.AWARD_STATUS='AWARDED' THEN PSV.VENDOR_NAME ELSE NULL END                        AS AWARDED_SUPPLIER,

PPV.SEGMENT1                                                                                               AS PROJECT_NUMBER,

PPV.NAME                                                                                                   AS PROJECT_NAME,

(SELECT PPNF.DISPLAY_NAME     

FROM   PJF_PROJECT_MEMBERS_V PPMV, 

       PJF_PROJ_ROLE_TYPES_VL PPRTV,

       PER_PERSON_NAMES_F PPNF                                                  

WHERE  PPNF.PERSON_ID=PPMV.PERSON_ID

AND    PPRTV.PROJECT_ROLE_ID=PPMV.PROJECT_ROLE_ID

AND    PPRTV.PROJECT_ROLE_NAME='PROJECT MANAGER'

AND    PPNF.NAME_TYPE='GLOBAL'

AND    PPMV.PROJECT_ID = PPV.PROJECT_ID

AND    SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND    SYSDATE BETWEEN PPMV.START_DATE_ACTIVE AND NVL(PPMV.END_DATE_ACTIVE,TO_DATE('31/12/4712','DD/MM/YYYY'))

AND    SYSDATE BETWEEN PPRTV.START_DATE_ACTIVE AND NVL(PPRTV.END_DATE_ACTIVE,TO_DATE('31/12/4712','DD/MM/YYYY'))

)                                                                              AS PROJECT_MANAGER,

PRHA.REQUISITION_NUMBER                                                                                            AS RELATED_REQUISITION_NUMBER,                                                                                                                                                      

POH.SEGMENT1                                                                                                        AS   ISSUED_PO_NUMBER,

PBH.BID_NUMBER                                                                                                 AS TOTAL_NO_OF_BIDS,                                                                         

PAIP.AWARDED_QUANTITY                                                                                                    AS NUMBER_OF_AWARDS,

PAIP.RECOMMENDED_AWARD_AMOUNT                                                                              AS TOTAL_ISSUED_RFQ_SUPPLIERS,

POH.SEGMENT1                                                                                                         AS TOTAL_ISSUED_POS,

PSV.VENDOR_ID                                                                             AS TOTAL_NUMBER_OF_SUPPLIERS_INVITED,

PBH.ORIGINAL_BID_NUMBER                                                                     AS TOTAL_NUMBER_OF_SUPPLIERS_ACKNOWLEDGED,

PAH.PUBLISH_DATE                                                                                 AS NEGOTIATION_PUBLISH_DATE,

ABS(TRUNC(PAH.CREATION_DATE)-TRUNC(PAH.CLOSE_DATE_PAUSE_ADJUSTED))                                  AS NEGOTIATION_CYCLE_TIME,

ABS(TRUNC(PAH.AWARD_APPROVAL_DATE)-TRUNC(PAH.AWARD_COMPLETE_DATE))                                  AS NEGOTIATION_AWARDCYCLE_TIME,

ABS(TRUNC(PAH.OPEN_BIDDING_DATE)-TRUNC(PAH.CLOSE_BIDDING_DATE))                                 AS NEGOTIATION_BIDDINGCYCLE_TIME,

ABS(TRUNC(PAH.PUBLISH_DATE)-TRUNC(PAH.CLOSE_DATE_PAUSE_ADJUSTED))                               AS  NEGOTIATION_PUBLICATION_TO_COMPLETION_DAYS, 

XLE.NAME                                                                                               AS BUYER_ENTITY,

(SELECT PPNF.DISPLAY_NAME     

FROM PER_PERSON_NAMES_F PPNF

WHERE PPNF.PERSON_ID=PRLA.REQUESTER_ID 

AND PPNF.NAME_TYPE='GLOBAL'

AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE)                                               AS REQUESTER 

--PSV.VENDOR_NAME AS                                                                                                SUPPLIER

FROM 

PON_AUCTION_HEADERS_ALL PAH,

FUN_NAMES_BUSINESS_UNITS_V FBU ,

PON_BID_HEADERS PBH ,

POZ_SUPPLIERS_V PSV  ,

 PON_AUC_DOCTYPES_VL PADV ,

 PON_NEGOTIATION_STYLES_VL PNSV ,

 PO_HEADERS_ALL POH ,

 PO_DISTRIBUTIONS_ALL         PDA,

PJF_PROJECTS_ALL_VL PPV ,

PON_BID_PO_NUMBERS PBPN  ,

PON_AUCTION_ITEM_PRICES PAIP ,

POR_REQUISITION_LINES_ALL PRLA ,

POR_REQUISITION_HEADERS_ALL PRHA ,

  XLE_ENTITY_PROFILES XLE 

 

--PO_LINES_ALL PLA,  

WHERE 1=1

AND PAH.PRC_BU_ID = FBU.BU_ID

AND PAH.AUCTION_HEADER_ID = PBH.AUCTION_HEADER_ID(+)

AND PBH.VENDOR_ID = PSV.VENDOR_ID(+) 

AND PAH.DOCTYPE_ID  = PADV.DOCTYPE_ID(+) 

AND PADV.NAME='RFQ'  

AND PAH.STYLE_ID=PNSV.STYLE_ID(+) 

AND PAH.AUCTION_HEADER_ID=POH.PO_HEADER_ID(+)

AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID(+)

AND PDA.PJC_PROJECT_ID = PPV.PROJECT_ID(+) 

--AND PAH.PROJECT_ID = PPV.PROJECT_ID(+)

AND PAH.AUCTION_HEADER_ID = PBPN.AUCTION_HEADER_ID(+) 

AND PAH.AUCTION_HEADER_ID(+) = PAIP.AUCTION_HEADER_ID

AND PAH.AUCTION_HEADER_ID = PRLA.AUCTION_HEADER_ID(+)

AND PRLA.BID_NUMBER (+) = PBH.BID_NUMBER 

AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)

AND PRLA.PO_HEADER_ID = POH.PO_HEADER_ID(+) 

AND PBPN.PO_HEADER_ID = POH.PO_HEADER_ID(+)

AND POH.SOLDTO_LE_ID = XLE.LEGAL_ENTITY_ID(+) 

--AND POH.PO_HEADER_ID=PLA.PO_HEADER_ID

--AND PBH.BID_NUMBER = PAIP.BEST_BID_BID_NUMBER(+)

--AND PAH.DOCUMENT_NUMBER ='ITNEG10021,1'

AND TRUNC(NVL(PAH.CREATION_DATE,SYSDATE)) BETWEEN NVL(:P_RFQ_CREATION_DATE_FROM,TRUNC(SYSDATE-999)) AND NVL(:P_RFQ_CREATION_DATE_TO,TRUNC(SYSDATE+999))

AND (FBU.BU_ID IN (:P_BUSINESS_UNIT_ID) OR COALESCE(:P_BUSINESS_UNIT_ID,NULL) IS NULL)

AND (PSV.VENDOR_ID IN (:P_VENDOR_ID) OR COALESCE(:P_VENDOR_ID,NULL) IS NULL)

AND (PPV.PROJECT_ID IN (:P_PROJECT_ID) OR COALESCE (:P_PROJECT_ID,NULL) IS NULL) 

AND (PAH.AUCTION_HEADER_ID IN (:P_AUCTION_HEADER_ID) OR COALESCE(:P_AUCTION_HEADER_ID,NULL) IS NULL)

AND (PRHA.REQUISITION_HEADER_ID IN (:P_REQUISITION_HEADER_ID) OR COALESCE(:P_REQUISITION_HEADER_ID,NULL) IS NULL)

AND  (XLE.LEGAL_ENTITY_ID IN (:P_LEGAL_ENTITY_ID) OR COALESCE (:P_LEGAL_ENTITY_ID,NULL) IS NULL)

)

WHERE 1=1

AND (PROJECT_MANAGER IN (:P_PROJECT_MANAGER_SOURCE_ID) OR COALESCE (:P_PROJECT_MANAGER_SOURCE_ID,NULL) IS NULL)

AND (BUYER IN (:P_ACTIVITY_OWNER_ID) OR COALESCE (:P_ACTIVITY_OWNER_ID,NULL) IS NULL)

GROUP BY

      BUSINESS_UNIT,

  RFQ_NUMBER,

  NEGOTIATION_TITLE,

  NEGOTIATION_SYNOPSIS,

  NEGOTIATION_TYPE,

  NEGOTIATION_STYLE,

      NEGOTIATION_STATUS,

      NEGOTIATION_CURRENCY,

  OUTCOME_DOCUMENT_TYPE,

      NEGOTIATION_CREATION_DATE,

  NEGOTIATION_PREVIEW_DATE,

  NEGOTIATION_OPEN_DATE,

  TIME_REMAINING,

      NEGOTIATION_CLOSE_DATE,

  BUYER, 

      AWARD,

      AWARD_DATE,      

      AWARD_NUMBER,

      AWARD_STATUS,

      AWARD_APPROVED_DATE,

  AWARDED_SUPPLIER,

  PROJECT_NUMBER,

  PROJECT_NAME,

  PROJECT_MANAGER,

  RELATED_REQUISITION_NUMBER,   

  ISSUED_PO_NUMBER,  

  NEGOTIATION_PUBLISH_DATE,

  NEGOTIATION_CYCLE_TIME,

  NEGOTIATION_AWARDCYCLE_TIME,

  NEGOTIATION_BIDDINGCYCLE_TIME,

  NEGOTIATION_PUBLICATION_TO_COMPLETION_DAYS,

  BUYER_ENTITY,

  REQUESTER

  --SUPPLIER

ORDER BY

  BUSINESS_UNIT,

  RFQ_NUMBER

Query to get Payables and Receivables Project Details (Project Cash Flow Report)

with gl_period_dates as ( 

       select period_name, 

              year_start_date, 

              add_months(year_start_date,12)-1 year_end_date, 

              quarter_start_date, 

              add_months(quarter_start_date,3)-1 quarter_end_date,

              start_date period_start_date, 

              end_date period_end_date

       from   gl_periods 

       where  period_set_name='Global Calendar'

       and    period_year=:p_year 

       and   (quarter_num IN (:p_quarter) OR coalesce(:p_quarter, NULL) IS NULL)

       and   (period_name IN (:p_month) OR coalesce(:p_month, NULL) IS NULL)),

receivables as (

select fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number,

              SUM(NVL(aps.amount_applied,0)) cash_in,

-- SUM(NVL(xal.ACCOUNTED_DR, 0)-NVL(xal.ACCOUNTED_CR, 0)) billing_amt

(SELECT sum(NVL(PROJECT_CURR_BILLED_AMT,0)) 

          FROM

          pjb_inv_line_dists idl

          

          WHERE 1=1

          and idl.CONTRACT_ID = och.id

          and  invoice_id in ( select invoice_id from pjb_invoice_headers 

  where

          contract_id = idl.contract_id  

  and invoice_status_code = 'ACCEPTED')

          ) billing_amt

FROM   ra_customer_trx_all rct,

okc_k_headers_all_b och,

xla_transaction_entities xlate,

xla_events xet,

xla_ae_headers xah,

xla_ae_lines xal,

ar_payment_schedules_all aps,

fun_names_business_units_v fnbuv,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl,

              (select distinct project_id, 

                      contract_id, 

                      major_version

               from   pjb_cntrct_proj_links ) pcpl

WHERE to_char(rct.interface_header_attribute1) = to_char(och.contract_number)

AND   to_char(rct.interface_header_attribute2) = to_char(och.id)

AND   rct.set_of_books_id = xah.ledger_id

       and   pcpl.contract_id=och.id

       and   pcpl.project_id=ppavl.project_id

       --and   och.version_type='C'

AND   och.major_version = (

         SELECT max(och1.major_version)

         FROM okc_k_headers_all_b och1

         WHERE och1.id = OCH.id)

       AND   pcpl.major_version = och.major_version  

       AND   xet.entity_id = xlate.entity_id

       AND   xet.application_id = xlate.application_id

       AND   rct.customer_trx_id = xlate.source_id_int_1

       AND   xal.ae_header_id = xah.ae_header_id

       AND   xet.event_id = xah.event_id

       AND   xah.ledger_id = Xal.ledger_id

   AND   xlp.LEGAL_EMPLOYER_FLAG='Y'

       AND   xal.accounting_class_code = 'RECEIVABLE'

       AND   rct.customer_trx_id = aps.customer_trx_id

       AND   fnbuv.bu_id = rct.org_id

       AND   xlp.legal_entity_id = rct.legal_entity_id

       and   xal.period_name = gpd.period_name

   AND   xlate.entity_code='TRANSACTIONS'

   and   xlate.ledger_id=xah.ledger_id

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (och.id IN (:p_contract_id) OR COALESCE(:p_contract_id, NULL) IS NULL)

and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id)

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

          och.id,

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number),

payables as (

SELECT fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1, 

SUM(NVL(aipa.INVOICE_BASE_AMOUNT, 0)) cash_out,

SUM(NVL(xal.ACCOUNTED_DR, 0)) liability_amt

FROM   ap_invoices_all aia,

ap_invoice_payments_all aipa,

fun_names_business_units_v fnbuv,

xla_transaction_entities xte,

xla_events xe,

xla_ae_headers xah,

xla_ae_lines xal,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl

WHERE  1 = 1

AND   aipa.invoice_id = aia.invoice_id

       and   aia.project_id=ppavl.project_id

AND   fnbuv.bu_id = aia.org_id

AND   aia.invoice_id = xte.source_id_int_1

AND   aia.set_of_books_id = xah.ledger_id

AND   xe.entity_id = xah.entity_id

AND   xte.entity_id = xe.entity_id

AND   xal.ae_header_id = xah.ae_header_id

AND   xte.application_id = xah.application_id

and   xte.ledger_id=xah.ledger_id

    AND   xte.entity_code='TRANSACTIONS'

AND   xal.accounting_class_code = 'LIABILITY'

AND   xlp.legal_entity_id = aia.legal_entity_id

       and   xal.period_name = gpd.period_name

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id) 

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1)

SELECT ar.carrying_out_organization_id,

       ar.bu_name,  

       ar.le_name,        

ar.segment1, 

       ar.name project_name, 

       nvl(ar.cash_in,0) cash_in,

nvl(ar.billing_amt,0) billing_amt,

nvl(ap.cash_out,0) cash_out,

nvl(ap.liability_amt,0) liability_amt,

(nvl(ar.cash_in,0)-nvl(ap.cash_out,0)) cash_flow,

(nvl(ar.billing_amt,0)-nvl(ar.cash_in,0)) receivable_amt,

(nvl(ap.liability_amt,0)-nvl(ap.cash_out,0)) payable_amt

FROM   receivables ar, 

       payables ap 

WHERE  ar.bu_id=ap.bu_id(+)

AND    ar.legal_entity_id=ap.legal_entity_id(+)

AND    ar.project_id=ap.project_id (+)

order by ar.segment1,

       ar.bu_name, 

       ar.le_name

Query to get Invoice Header Details

 select invoice_num,

       INVOICE_DATE,

   supplier_name,

   supplier_number,

   business_unit,

   inv_currency,

   INVOICE_AMOUNT,

   conversion_rate_gbp*INVOICE_AMOUNT as inv_amt_gbp,

   AMOUNT_PAID,

   conversion_rate_gbp*AMOUNT_PAID inv_amt_paid_gbp,

   inv_pay_status,

   PAYMENT_DATE

FROM (  

select distinct aia.invoice_num, --

       aia.INVOICE_AMOUNT,

   aia.SOURCE,

   aia.AMOUNT_PAID,

   aia.INVOICE_CURRENCY_CODE inv_currency, --

   aia.INVOICE_TYPE_LOOKUP_CODE,

   to_char(aia.INVOICE_DATE, 'dd-Mon-yy') INVOICE_DATE, -- 

   aia.TAXATION_COUNTRY country,

   supp.vendor_name supplier_name, --

   supp.segment1 supplier_number, --

   hao.NAME business_unit, -- 

   att.NAME payment_terms,

   case 

when aia.INVOICE_CURRENCY_CODE = 'GBP'

  THEN 1

else 

  TRUNC((select conversion_rate

   from gl_daily_rates

   where 1=1

   and from_currency = aia.INVOICE_CURRENCY_CODE

   and to_currency = 'GBP'

   and trunc(conversion_date) = trunc(ipa.PAYMENT_DATE)/*(SELECT MAX(conversion_date) 

                                 from gl_daily_rates 

where 1=1

and from_currency = aia.INVOICE_CURRENCY_CODE

and conversion_type = 'Spot'

and to_currency = 'GBP')*/

   and conversion_type = 'Spot'

   ),10)

end conversion_rate_gbp ,

        CASE 

      WHEN aia.PAYMENT_STATUS_FLAG = 'N'

         THEN 'Not Paid'

         WHEN aia.PAYMENT_STATUS_FLAG = 'P'  

THEN 'Partially Paid'

         ELSE 'Fully Paid'  

        END inv_pay_status,

to_char(ipa.PAYMENT_DATE, 'dd-Mon-yyyy') PAYMENT_DATE

        /*inv_pay.amount inv_amt_paid,

        inv_pay.payment_num,

        inv_pay.period_name,

        inv_pay.accounting_date,

        inv_pay.posted_flag,

        inv_pay.creation_date */

from AP_INVOICES_ALL aia,

POZ_SUPPLIERS_V supp,

AP_TERMS_TL att,

HR_ALL_ORGANIZATION_UNITS hao,

AP_INVOICE_PAYMENTS_ALL inv_pay,

ap_checks_all aca,

iby_payments_all ipa

where 1=1

and aia.party_id = supp.party_id

and aia.org_id = hao.ORGANIZATION_ID

and aia.TERMS_ID = att.TERM_ID

and att.LANGUAGE = 'US'

and aia.PAYMENT_STATUS_FLAG != 'N'

--and aia.PAYMENT_STATUS_FLAG = 'P'  --

and inv_pay.invoice_id = aia.invoice_id

and aca.check_id = inv_pay.check_id

and aca.STATUS_LOOKUP_CODE = 'CLEARED'  --  temp to be tested

and ipa.payment_id = aca.payment_id

and ipa.PAYMENT_DATE between :p_start_date and :p_end_date

--and aia.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'

--and aia.invoice_num in ('ERS-2021-10-11-28038','4154615465','9442040', '8185047')

--'ERS-2022-06-29-115421'   --'51579578'  --'2203929689'  --'0000002923'  'ERS-2021-10-11-28038'

--AND LINE_SOURCE = 'ETAX'

)

Query for Credit Hold Report Oracle Erp Cloud

 SELECT DISTINCT DOFL.SOURCE_ORDER_NUMBER

                ,DOFL.SOURCE_LINE_NUMBER 

, DHCT.HOLD_DESCRIPTION

            ,DHCB.hold_code

,DHCT.HOLD_NAME

,DHA.ORDER_NUMBER

,EGT.DESCRIPTION ITEM_DESC

,EGB.ITEM_NUMBER    ITEM

,HP.PARTY_NAME 

,HCA.ATTRIBUTE1

,DHI.APPLY_DATE

,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY

, hou.name       OU

, hca.account_number

,DHI.HOLD_COMMENTS

, (SELECT HCP.CREDIT_HOLD

   FROM   HZ_CUSTOMER_PROFILES_F HCP

   WHERE  HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

   AND ROWNUM = 1

   ) ACC_CREDIT_HOLD

from DOO_HOLD_CODES_TL      DHCT 

   , DOO_HOLD_CODES_B       DHCB

   , DOO_HOLD_INSTANCES     DHI

   , DOO_ORDER_FLINES_V     DOFL

   , DOO_HEADERS_ALL        DHA

   , egp_system_items_b     egb

   , egp_system_items_TL egt

   , HZ_PARTIES             HP

   , HZ_CUST_ACCOUNTS       HCA

   , PER_USERS              PU

   , PER_PERSON_NAMES_F     PPNF

   , HR_OPERATING_UNITS     hou

WHERE 1=1

AND DHCT.LANGUAGE                 = USERENV('LANG')

AND DHCB.HOLD_CODE_ID             = DHCT.HOLD_CODE_ID

AND DHCB.HOLD_CODE_ID             = DHI.HOLD_CODE_ID

AND DHI.TRANSACTION_ENTITY_NAME1  = 'DOO_ORDER_FLINES_V'

AND DHI.TRANSACTION_ENTITY_ID1  = DOFL.FULFILL_LINE_ID   

AND DOFL.HEADER_ID                = DHA.HEADER_ID

AND DOFL.INVENTORY_ITEM_ID        = EGB.INVENTORY_ITEM_ID

AND DOFL.INVENTORY_ORGANIZATION_ID = EGB.ORGANIZATION_ID

AND EGB.INVENTORY_ITEM_ID         = EGT.INVENTORY_ITEM_ID

AND  EGB.ORGANIZATION_ID           = EGT.ORGANIZATION_ID

AND  DHA.SOLD_TO_PARTY_ID          = HP.PARTY_ID

AND  HP.PARTY_ID                   = HCA.PARTY_ID

AND  DHI.RELEASE_DATE IS  NULL

AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)

AND  DHI.CREATED_BY                = PU.USERNAME

AND PU.PERSON_ID                   = PPNF.PERSON_ID(+)

AND PPNF.NAME_TYPE  (+)            ='GLOBAL'

AND dha.org_id                    = hou.organization_id

AND hou.name                      = :P_Business_Unit

AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)

UNION ALL

SELECT DISTINCT DHA.SOURCE_ORDER_NUMBER

                ,DLA.SOURCE_LINE_NUMBER 

, DHCT.HOLD_DESCRIPTION

            ,DHCB.hold_code

,DHCT.HOLD_NAME

,DHA.ORDER_NUMBER

,EGT.DESCRIPTION ITEM_DESC

,EGB.ITEM_NUMBER    ITEM

,HP.PARTY_NAME 

,HCA.ATTRIBUTE1

,DHI.APPLY_DATE

,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY

, hou.name       OU

, hca.account_number

,DHI.HOLD_COMMENTS

, (SELECT HCP.CREDIT_HOLD

   FROM   HZ_CUSTOMER_PROFILES_F HCP

   WHERE  HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

   AND ROWNUM = 1

   ) ACC_CREDIT_HOLD

from DOO_HOLD_CODES_TL      DHCT 

   , DOO_HOLD_CODES_B       DHCB

   , DOO_HOLD_INSTANCES     DHI

   , DOO_HEADERS_ALL        DHA

   , DOO_LINES_ALL          DLA

   , egp_system_items_b     egb

   , egp_system_items_TL egt

   , HZ_PARTIES             HP

   , HZ_CUST_ACCOUNTS       HCA

   , PER_USERS              PU

   , PER_PERSON_NAMES_F     PPNF

   , HR_OPERATING_UNITS     hou

WHERE 1=1

AND DHCT.LANGUAGE                 = USERENV('LANG')

AND DHCB.HOLD_CODE_ID             = DHCT.HOLD_CODE_ID

AND DHCB.HOLD_CODE_ID             = DHI.HOLD_CODE_ID

AND DHI.TRANSACTION_ENTITY_NAME1  = 'DOO_ORDER_HEADERS_V'

AND DHI.TRANSACTION_ENTITY_ID1  = DHA.HEADER_ID   

AND DHA.HEADER_ID                  =  DLA.HEADER_ID

AND DLA.INVENTORY_ITEM_ID          = EGB.INVENTORY_ITEM_ID

AND DLA.INVENTORY_ORGANIZATION_ID  = EGB.ORGANIZATION_ID

AND EGB.INVENTORY_ITEM_ID          = EGT.INVENTORY_ITEM_ID

AND  EGB.ORGANIZATION_ID           = EGT.ORGANIZATION_ID

AND  DHA.SOLD_TO_PARTY_ID          = HP.PARTY_ID

AND  HP.PARTY_ID                   = HCA.PARTY_ID

AND  DHI.RELEASE_DATE IS  NULL

AND  DHI.CREATED_BY                = PU.USERNAME

AND PU.PERSON_ID                   = PPNF.PERSON_ID(+)

AND PPNF.NAME_TYPE  (+)            ='GLOBAL'

AND dha.org_id                    = hou.organization_id

AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)

AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)

ORDER BY 1,6