[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* PROCEDURE insert_accrual_reconcile
(p_transaction_id number,
p_po_line_location_id number,
p_po_distribution_id number,
p_shipment_line_id number,
p_organization_id number,
p_transaction_date date,
p_transaction_amount number,
p_accrual_account_id number
)
IS
v_operating_unit org_organization_definitions.operating_unit % type;
SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id = p_organization_id;
SELECT source_document_code,
unit_of_measure,
shipment_header_id,
po_header_id,
po_line_id,
po_unit_price,
requisition_line_id,
vendor_id,
quantity
FROM rcv_transactions
WHERE transaction_id = p_transaction_id;
SELECT line_num
FROM po_lines_all
WHERE po_line_id = v_rcv_rec.po_line_id;
SELECT vendor_name
FROM po_vendors
WHERE vendor_id = v_rcv_rec.vendor_id;
SELECT item_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_shipment_line_id;
SELECT receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = v_rcv_rec.shipment_header_id;
SELECT segment1
FROM po_headers_all
WHERE po_header_id = v_rcv_rec.po_header_id;
SELECT primary_unit_of_measure
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = v_item_id;
For price_rec IN (SELECT list_price_per_unit price
FROM mtl_system_items
WHERE inventory_item_id = v_item_id
AND organization_id = p_organization_id)
LOOP
v_unit_price := price_rec.price;
For price_rec IN (SELECT unit_price
FROM po_requisition_lines_all
WHERE requisition_line_id = v_rcv_rec.requisition_line_id)
LOOP
v_unit_price := price_rec.unit_price;
INSERT INTO po_accrual_reconcile_temp_all
(transaction_date,
inventory_item_id,
transaction_quantity,
po_header_id,
po_line_num,
po_line_id,
vendor_name,
transaction_organization_id,
vendor_id,
item_master_organization_id,
accrual_account_id,
accrual_code,
po_transaction_type,
receipt_num,
po_transaction_id,
po_unit_of_measure,
primary_unit_of_measure,
net_po_line_quantity,
po_num,
po_distribution_id,
transaction_unit_price,
avg_receipt_price,
transaction_amount,
transaction_source_code,
write_off_flag,
destination_type_code,
net_po_line_amount,
aging_date,
org_id,
line_location_id)
VALUES (p_transaction_date,
v_item_id,
-v_rcv_rec.quantity,
v_rcv_rec.po_header_id,
v_line_num,
v_rcv_rec.po_line_id,
v_vendor_name,
p_organization_id,
v_rcv_rec.vendor_id,
v_operating_unit,
p_accrual_account_id,
lv_accrual_code, --'Receive',
lv_po_trans_type, --'RECEIVE', -- Modified by Ramananda for removal of SQL LITERALs :bug#4428980
v_receipt_num,
p_transaction_id,
v_rcv_rec.unit_of_measure,
v_primary_uom,
- (v_rcv_rec.quantity * 2),
v_po_num,
p_po_distribution_id,
v_unit_price,
-(v_unit_price / 2),
-p_transaction_amount,
v_rcv_rec.source_document_code,
'N',
lv_destination_type_code, --'INVENTORY',
-p_transaction_amount,
p_transaction_date,
v_operating_unit,
p_po_line_location_id);
END insert_accrual_reconcile;
PROCEDURE insert_line
( v_code IN VARCHAR2,
v_line_loc_id IN NUMBER,
v_po_hdr_id IN NUMBER,
v_po_line_id IN NUMBER,
v_cre_dt IN DATE,
v_cre_by IN NUMBER,
v_last_upd_dt IN DATE,
v_last_upd_by IN NUMBER,
v_last_upd_login IN NUMBER,
flag IN VARCHAR2,
v_service_type_code IN VARCHAR2 DEFAULT NULL)
IS
v_seq_val NUMBER;
CURSOR Fetch_Focus_Id IS SELECT JAI_PO_LINE_LOCATIONS_S.NEXTVAL
FROM Dual;
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.insert_line';
INSERT INTO JAI_PO_LINE_LOCATIONS( Line_Focus_Id, Line_Location_Id, Po_Line_Id, Po_Header_Id,
Tax_Modified_Flag, Tax_Amount, Total_Amount,
Creation_Date, Created_By, Last_Update_Date, Last_Updated_By,
Last_Update_Login,Service_type_code )
VALUES
( v_seq_val, v_line_loc_id , v_po_line_id, v_po_hdr_id,
'N', v_tax_amt, v_total_amt,
v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by,
v_last_upd_login,v_service_type_code );
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Modified_flag = 'N',
Tax_Amount = 0,
Total_Amount = 0,
Last_Update_Date = v_last_upd_dt,
Last_Updated_By = v_last_upd_by,
Last_Update_Login = v_last_upd_login
WHERE Line_Location_Id = v_line_Loc_id AND
Po_Line_Id = v_po_line_id AND
Po_Header_Id = v_po_hdr_id;
END insert_line;
CURSOR Get_Inv_Org_Id_Cur IS SELECT Inventory_Organization_Id
FROM Hr_Locations
WHERE Location_Id = v_location_id;
CURSOR Fetch_Location_Id_Cur IS SELECT Ship_To_Location_Id
FROM Po_Headers_All
WHERE Po_Header_Id = p_po_header_id;
SELECT Rate, Rate_Date, Rate_Type
FROM Po_Headers_All
WHERE Po_Header_Id = p_po_header_id;
CURSOR Fetch_Org_Id_Cur IS SELECT NVL( Org_Id, -99 )
FROM Po_Headers_All
WHERE Po_Header_Id = p_po_header_id;
SELECT Quantity, Line_Location_Id
FROM Po_Line_Locations_All
WHERE Po_Header_Id = p_header_id
AND Po_Line_Id = v_po_line_id
and SYSDATE between nvl(start_date, SYSDATE) and nvl(end_date, SYSDATE) -- cbabu for Bug# 2740918
ORDER BY Quantity;
SELECT Po_Line_Id, NVL( Price_Break_Lookup_Code, 'NC' ) Price_Break_Lookup_Code
FROM Po_Lines_All
WHERE Po_Header_Id = p_header_id
AND Line_Num = p_line_num;
SELECT NVL( Price_Break_Lookup_Code, 'NC' ) Price_Break_Lookup_Code
FROM Po_Lines_All
WHERE Po_Line_Id = p_line_id;
SELECT SUM( Quantity )
FROM Po_Line_Locations_All
WHERE Po_Line_Id = v_po_line_id;
SELECT COUNT( * )
FROM Po_Line_Locations_All
WHERE Po_Line_Id = v_po_line_id;
select vendor_id,vendor_site_id
from po_headers_all
where po_header_id=v_po_hdr_id;
SELECT line_location_id, tax_line_no, po_line_id, po_header_id,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
tax_id, currency, tax_rate, qty_rate, uom, tax_amount, tax_type,
vendor_id, modvat_flag, tax_target_amount,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES
WHERE line_location_id = v_src_ship_id
AND po_line_id = v_po_line_id;
CURSOR Fetch_Focus_Id IS SELECT JAI_PO_LINE_LOCATIONS_S.NEXTVAL
FROM Dual;
CURSOR Fetch_Cum_Pr_Cur IS SELECT Price_Break_Lookup_Code
FROM Po_Lines_All
WHERE Po_Line_Id = v_po_line_id;
SELECT tax_category_id
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = p_line_location_id;
INSERT INTO JAI_PO_LINE_LOCATIONS(
line_focus_id, line_location_id, po_line_id, po_header_id,
tax_modified_flag, tax_amount, total_amount,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
,service_type_code ) VALUES (
v_seq_val, v_line_loc_id, v_po_line_id, v_po_hdr_id,
'N', v_tax_amt, v_total_amt,
v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login,
v_tax_category_id_holder -- cbabu for EnhancementBug# 2427465
, v_service_type_code );
INSERT INTO JAI_PO_REQUEST_T(
Line_Focus_Id, Line_Location_Id, Tax_Line_No,
Po_Line_Id, Po_Header_Id, Precedence_1,
Precedence_2, Precedence_3, Precedence_4,
Precedence_5, Precedence_6,
Precedence_7, Precedence_8, Precedence_9,
Precedence_10,
Tax_Id, Currency,
Tax_Rate, Qty_Rate, UOM,
Tax_Amount, Tax_Type, Modvat_Flag,
Vendor_Id, Tax_Target_Amount, Creation_Date,
Created_By, Last_Update_Date, Last_Updated_By, Last_Update_Login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_seq_val, v_line_loc_id, Tax_Rec.Tax_Line_No,
v_Po_Line_Id, v_Po_Hdr_Id, Tax_Rec.Precedence_1,
Tax_Rec.Precedence_2, Tax_Rec.Precedence_3, Tax_Rec.Precedence_4,
Tax_Rec.Precedence_5, Tax_Rec.Precedence_6,
Tax_Rec.Precedence_7, Tax_Rec.Precedence_8, Tax_Rec.Precedence_9,
Tax_Rec.Precedence_10,
Tax_Rec.Tax_Id, Tax_Rec.Currency,
Tax_Rec.Tax_Rate, Tax_Rec.Qty_Rate, Tax_Rec.UOM,
Tax_Rec.Tax_Amount, Tax_Rec.Tax_Type, Tax_Rec.Modvat_Flag,
Tax_Rec.Vendor_Id, Tax_Rec.Tax_Target_Amount, v_cre_dt,
v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login,
tax_rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
CURSOR Get_Inv_Org_Id_Cur IS SELECT Inventory_Organization_Id
FROM Hr_Locations
WHERE Location_Id = v_location_id;