[Home] [Help]
11677: and bcc.Parent_Bc_Packet_Id is NULL;
11678:
11679: select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0)),SUM(POD.rate)/SUM(1)
11680: into l_com_summ,l_com_rate
11681: from pa_bc_commitments bcc,
11682: po_distributions pod --Added for Bug#3693974
11683: where pod.po_header_id = p_sys_ref2
11684: and pod.po_distribution_id = p_sys_ref3
11685: and ((
12603: and bcc.Parent_Bc_Packet_Id is NULL;
12604:
12605: CURSOR C_get_raw_bccmt_amt IS
12606: select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0))
12607: from pa_bc_commitments bcc
12608: where bcc.document_type = 'AP'
12609: and bcc.document_header_id = p_sys_ref2
12610: and bcc.document_distribution_id = p_sys_ref5
12611: and bcc.Parent_Bc_Packet_Id is NULL;
12998:
12999: -- This procedure inserts records into pa_bc_packets for relieving commitment raw and burden
13000: -- and also for reserving raw and burden against actual.
13001: -- 1. IF p_fc_document_type = 'CMT'/'ALL' then we need to relieve commitment amount.
13002: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
13003: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13004: -- amounts lying in pa_bc_commitments.
13005: -- else
13006: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13000: -- and also for reserving raw and burden against actual.
13001: -- 1. IF p_fc_document_type = 'CMT'/'ALL' then we need to relieve commitment amount.
13002: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
13003: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13004: -- amounts lying in pa_bc_commitments.
13005: -- else
13006: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13007: -- amounts lying in pa_bc_packets.
13008: -- end if;
13008: -- end if;
13009: -- 3.p_document type = 'ALL' and p_adj_exp_item_id IS NULL implies its a regular
13010: -- Payable Invoice getting interfaced to Projects. In this case
13011: -- insert records into pa_bc_packets to reserve actual raw cost amount which is equal to
13012: -- commitment amount in pa_bc_commitments relieved in step 2.
13013: -- Note : parent_bc_packet_id is populated to -1 such that pa_funds_check process will
13014: -- generate burden against this actual based on latest compiled set id.
13015: -- 4.If p_document type = 'ACT'/'ALL' and p_adj_exp_item_id IS NOT NULL then it implies thats its a
13016: -- reversing/cancelled Payable Invoice getting interfaced to Projects and original
13108: ,bcc.parent_bc_packet_id
13109: ,bcc.encumbrance_type_id
13110: ,bcc.document_header_id_2
13111: ,bcc.document_distribution_type
13112: From Pa_Bc_Commitments_all bcc
13113: Where bcc.Document_Header_Id = p_sys_ref2
13114: and bcc.Document_Distribution_Id =p_sys_ref5
13115: --PA-J Receipt accrual changes
13116: and bcc.document_type = 'AP'
13181: OR
13182: (bcc.parent_bc_packet_id is NOT null AND p_txn_source = 'AP VARIANCE')
13183: );
13184:
13185: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
13186: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
13187: L_RAW_BC_PACKET_ID NUMBER;
13188: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
13189: l_po_line_id po_distributions_all.po_line_id%TYPE;
13230:
13231: END IF;
13232:
13233: -- Code to derive values for variables which will decide the flow of this procedure
13234: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
13235: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
13236:
13237: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
13238:
14012: ,bcc.Org_Id
14013: ,bcc.Pa_Date
14014: ,bcc.bc_packet_id
14015: ,bcc.packet_id
14016: From Pa_Bc_Commitments_all bcc
14017: Where bcc.Document_Header_Id = p_sys_ref2
14018: and bcc.Document_Distribution_Id = p_sys_ref5
14019: and bcc.Parent_Bc_Packet_Id is NULL
14020: --PA-J Receipt accrual changes
14060: and bcc.actual_flag = 'E' -- Bug 5014138 : to pick just the encumbrance record
14061: -- R12 Ap lines Uptake : Added for cash based accounting which can have multiple packets associated witha distribution.
14062: and rownum = 1 ;
14063:
14064: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14065: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14066: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14067: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
14068: l_po_line_id po_distributions_all.po_line_id%TYPE;
14099: END IF;
14100:
14101: -- Code to derive values for variables which will decide the flow of this procedure
14102:
14103: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
14104: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
14105:
14106: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
14107:
14304: ,p_txn_interface_id --REL12
14305: ,bcc.encumbrance_type_id
14306: ,bcc.document_header_id_2
14307: ,bcc.document_distribution_type
14308: From Pa_Bc_Commitments_all bcc
14309: Where bcc.Document_Header_Id = p_sys_ref2 -- Added for using index
14310: and bcc.Document_Distribution_Id = p_sys_ref5
14311: and bcc.packet_id = g_packet_id_tbl(i)
14312: and bcc.document_type = 'AP' ;
14918: ,bcc.bc_packet_id
14919: ,bcc.packet_id
14920: ,bcc.bc_commitment_id
14921: ,bcc.vendor_id
14922: From Pa_Bc_Commitments_all bcc,
14923: po_distributions pod
14924: -- R12 Funds management Uptake : For fixing performance issues removing the code for CC which is obsolete for R12
14925: -- Modifying code to use Index on document_header_id and document_distribution_id of bc commitments
14926: where bcc.document_header_id = pod.po_header_id
14979: and bcc.Status_Code in('A','C')
14980: and bcc.Parent_Bc_Packet_Id is NULL
14981: Order By bcc.packet_id;
14982:
14983: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14984: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14985: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14986: l_po_line_id po_distributions_all.po_line_id%TYPE;
14987: l_expenditure_item_date pa_expenditure_items_all.expenditure_item_date%TYPE; -- Aded for 16451280
15007: END IF;
15008:
15009: -- Code to derive values for variables which will decide the flow of this procedure
15010:
15011: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
15012: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
15013:
15014: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
15015:
15231: ,p_txn_interface_id --REL12
15232: ,bcc.encumbrance_type_id
15233: ,bcc.document_header_id_2
15234: ,bcc.document_distribution_type
15235: From Pa_Bc_Commitments_all bcc
15236: Where bcc.bc_commitment_id in (SELECT bcc1.bc_commitment_id
15237: FROM PA_BC_COMMITMENTS_ALL bcc1
15238: WHERE bcc1.document_header_id = p_sys_ref2
15239: AND bcc1.document_distribution_id = p_sys_ref3
15233: ,bcc.document_header_id_2
15234: ,bcc.document_distribution_type
15235: From Pa_Bc_Commitments_all bcc
15236: Where bcc.bc_commitment_id in (SELECT bcc1.bc_commitment_id
15237: FROM PA_BC_COMMITMENTS_ALL bcc1
15238: WHERE bcc1.document_header_id = p_sys_ref2
15239: AND bcc1.document_distribution_id = p_sys_ref3
15240: AND bcc1.parent_bc_packet_id = g_bc_packet_id_tbl(i)
15241: UNIOn ALL