[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_select varchar2(2000);
v_po_select varchar2(2000);
-- Variables to store the Main select information
v_reqnum po_requisition_headers.segment1%type;
Delete from FV_STATUS_OBLIG_TEMP ;
select 2
from fv_status_oblig_temp
where inv_po_distribution_id = p_inv_po_distribution_id ;
select 1
from fv_status_oblig_temp
where po_req_distribution_id = p_po_req_distribution_id;
v_select :=
'select api.invoice_num,
api.invoice_date,
apd.amount,
apd.dist_code_combination_id,
apd.po_distribution_id ' || v_val_string
|| ' from ap_invoice_distributions apd,
ap_invoices api,
gl_code_combinations glcc
where api.invoice_id = apd.invoice_id
and glcc.code_combination_id = apd.dist_code_combination_id
and (api.invoice_date between :b_from_period and :b_to_period)
and api.set_of_books_id = :b_set_of_books_id '|| v_where ;
dbms_sql.parse(v_inv_cursor, v_select, DBMS_SQL.V7) ;
select poh.segment1,
pod.gl_encumbered_date,
(pod.quantity_ordered -
nvl(pod.quantity_cancelled,0))
* Nvl(pol.unit_price, 0),
pol.closed_code,
pod.code_combination_id ,
pod.req_distribution_id,
pod.po_distribution_id
Into v_oblignum ,
v_obligdate ,
v_obligamt ,
v_obligstatus ,
v_obligccid ,
v_po_req_distribution_id,
v_po_distribution_id
from po_headers poh,
po_lines pol,
po_line_locations poll,
po_distributions pod,
gl_code_combinations glcc
where poh.approved_flag = 'Y'
and pod.po_distribution_id = v_inv_po_distribution_id
and poh.po_header_id = pol.po_header_id
and pol.po_line_id = poll.po_line_id
and poll.line_location_id = pod.line_location_id
and pod.code_combination_id = glcc.code_combination_id
and pod.set_of_books_id = v_set_of_books_id
and not exists
(select 1
from po_headers A
where A.segment1 = poh.segment1
and poh.type_lookup_code = 'PLANNED'
and pod.source_distribution_id is NULL);
(select 2
from fv_status_oblig_temp
where inv_po_distribution_id = pod.po_distribution_id) ;*/
Select porh.segment1,
pord.gl_encumbered_date,
(porl.quantity - nvl(porl.quantity_cancelled,0))
* porl.unit_price,
pord.code_combination_id
Into v_reqnum ,
v_reqdate ,
v_reqamt,
v_reqccid
from po_requisition_headers porh,
po_requisition_lines porl,
po_req_distributions pord,
gl_code_combinations glcc
where pord.distribution_id = v_po_req_distribution_id
and porh.requisition_header_id =
porl.requisition_header_id
and porl.requisition_line_id =
pord.requisition_line_id
and pord.code_combination_id =
glcc.code_combination_id;
(select 1
from fv_status_oblig_temp
where po_req_distribution_id =
v_po_req_distribution_id ) ;*/
Insert_Processing ;
v_po_select :=
'select poh.segment1 ,
pod.gl_encumbered_date,
(pod.quantity_ordered - nvl(pod.quantity_cancelled,0))
* nvl(pol.unit_price, 0) obligamt ,
pol.closed_code,
pod.code_combination_id,
pod.req_distribution_id,
pod.po_distribution_id' || v_val_string ||
' from po_headers poh,
po_lines pol,
po_line_locations poll,
po_distributions pod,
gl_code_combinations glcc
where
NOT EXISTS( Select 1
from fv_status_oblig_temp fvs
where fvs.po_distribution_id = pod.po_distribution_id)
and poh.approved_flag = '||''''||'Y'||''''||
' and poh.po_header_id = pol.po_header_id
and pol.po_line_id = poll.po_line_id
and poll.line_location_id = pod.line_location_id
and not exists
(select 1
from po_headers A
where A.segment1 = poh.segment1
and poh.type_lookup_code = '||''''||'PLANNED'||''''||
' and pod.source_distribution_id is NULL)
and pod.set_of_books_id = :b_set_of_books_id
and glcc.code_combination_id = pod.code_combination_id
and pod.gl_encumbered_date between :b_from_period and :b_to_period '|| v_where ;
Insert into surya_temp values('PO', v_po_select) ;
dbms_sql.parse(v_po_cursor, v_po_select, DBMS_SQL.V7) ;
Select porh.segment1,
pord.gl_encumbered_date,
(porl.quantity - nvl(porl.quantity_cancelled,0))
* porl.unit_price ,
pord.code_combination_id
Into v_reqnum ,
v_reqdate ,
v_reqamt,
v_reqccid
from po_requisition_headers porh,
po_requisition_lines porl,
po_req_distributions pord,
gl_code_combinations glcc
where pord.distribution_id = v_po_req_distribution_id
and porh.requisition_header_id = porl.requisition_header_id
and porl.requisition_line_id = pord.requisition_line_id
and pord.code_combination_id = glcc.code_combination_id ;
Insert_Processing ;
Procedure Insert_processing is
l_module_name varchar2(200) := g_module_name || 'insert_processing';
-- Perform the Inserts
insert into fv_status_oblig_temp
(REQNUM ,
REQDATE ,
REQAMT ,
REQCCID ,
OBLIGNUM ,
OBLIGDATE ,
OBLIGAMT ,
OBLIGCCID ,
OBLIGSTATUS ,
INVNUM ,
INVDATE ,
INVAMT ,
INVCCID ,
segval1 ,
segval2 ,
segval3 ,
INV_PO_DISTRIBUTION_ID ,
PO_REQ_DISTRIBUTION_ID ,
PO_DISTRIBUTION_ID )
values
(v_reqnum ,
v_reqdate ,
v_reqamt ,
v_reqccid ,
v_oblignum ,
v_obligdate ,
v_obligamt ,
v_obligccid ,
v_obligstatus,
v_invnum ,
v_invdate ,
v_invamt ,
v_invccid,
v_seg_val1,
v_seg_val2,
v_seg_val3,
v_inv_po_distribution_id,
v_po_req_distribution_id,
v_po_distribution_id ) ;
End Insert_Processing ;