[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Sysdate INTO l_today FROM dual;
/* This is for Insert the Reservation_id into CSP_REPAIR_PO_LINES table
all the reservation made for defective parts
*/
l_out_reservation_rec.need_by_date := l_reservation_rec.need_by_date;
Select VENDOR_NAME
into l_VENDOR_NAME
from po_vendors
where vendor_id = P_repair_supplier_id
--and nvl(start_date_active,sysdate) <= nvl(end_date_active,sysdate);
Select organization_id
into L_repair_supplier_org_id ----> P_repair_supplier_org_id
from hr_organization_information
where ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
and ORG_INFORMATION3 = P_repair_supplier_id; ---> 1159 (Vendor Id parameter)
SELECT LOCATION_ID
INTO l_supplier_org_location_id
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = L_repair_supplier_org_id;
select a.vendor_id
from po_vendors a, hr_organization_information b
where b.organization_id = 3201 --> P_repair_supplier_org_id (Repair supplier Org Id parameter)
and a.vendor_id = b.ORG_INFORMATION3
and b.ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
and NVL(a.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
and NVL(a.END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
Select MEANING, LOOKUP_CODE
into l_MEANING, l_LOOKUP_CODE
from mfg_lookups
Where LOOKUP_TYPE = 'INV_REPAIR_PROGRAMS' --'MRP_REPAIR_PROGRAM_DEFINITIONS'
and ENABLED_FLAG = 'Y'
and NVL(START_DATE_ACTIVE,SYSDATE) <= SYSDATE
and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
and lookup_code = p_repair_program;
select csp_repair_po_headers_s1.nextval
into l_repair_po_header_id
from dual;
/* In "CSP_PARTS_ORDER.PROCESS_PURCHASE_REQ" API inserting into
"PO_REQUISITIONS_INTERFACE_ALL" table SOURCE_TYPE_CODE as "VENDOR".
So this item must be Purchasing_enabled in both Repair supplier org and destination org. */
L_REPAIR_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_repair_supplier_org_id);
SELECT LOCATION_ID
INTO l_supplier_org_location_id
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = L_repair_supplier_org_id;
SELECT LOCATION_ID
INTO l_ship_to_location_id
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = P_dest_organization_id;
SELECT SHIP_METHOD
INTO l_shipping_method_code
FROM MTL_INTERORG_SHIP_METHODS
WHERE FROM_ORGANIZATION_ID = P_repair_supplier_org_id
AND TO_ORGANIZATION_ID = P_dest_organization_id
AND FROM_LOCATION_ID = l_supplier_org_location_id
AND TO_LOCATION_ID = l_ship_to_location_id
AND DEFAULT_FLAG = 1;
This API Inserts record into PO_REQUISITIONS_INTERFACE_ALL table
*/
---------------------------------------------------------------------
/* REQUISITION_TYPE is passed as "PURCHASE" in the follwoing API.
but this is not in valid values such as BLANKET,PLANNED,SCHEDULED and STANDARD
*/
--dbms_output.put_line('First l_header_rec.requisition_header_id '||l_header_rec.requisition_header_id);
select po_notes_s.nextval
into p_note_id
from dual;
Insert into PO_NOTES
(
PO_NOTE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,TITLE
,USAGE_ID
,NOTE_TYPE
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,DOCUMENT_ID
,APP_SOURCE_VERSION
,NOTE
)
VALUES
(
p_note_id
,SYSDATE
,l_user_id
,l_login_id
,SYSDATE
,l_user_id
,'REPAIR AND RETURN: DEFECTIVE PARTS DETAILS'
,3 -- 'Note to Buyer'
,'S'
,SYSDATE
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL --p_document_id
,'1.0'
,p_note
);
fnd_documents_pkg.Insert_Row
(X_Rowid => X_Rowid,
X_document_id => X_document_id,
X_creation_date => SYSDATE,
X_created_by => l_user_id,
X_last_update_date => SYSDATE,
X_last_updated_by => l_user_id,
X_last_update_login => l_login_id,
X_datatype_id => 2, -- Longtext
X_category_id => 34, -- To Buyer
X_security_type => 4, -- None
X_security_id => NULL,
X_publish_flag => 'Y',
X_image_type => NULL,
X_storage_type => NULL, -- 1
X_usage_type => 'O', -- 'S'(Standard)
X_start_date_active => SYSDATE,
X_end_date_active => NULL,
X_request_id => NULL,
X_program_application_id => NULL,
X_program_id => NULL,
X_program_update_date => NULL,
X_language => USERENV('LANG'),
X_description => 'Repair Purchase Order Defective Parts Details: '||L_REPAIR_PO_HEADER_ID,
X_file_name => NULL,
X_media_id => X_media_id,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_create_doc => 'N');
INSERT INTO fnd_documents_long_text
(MEDIA_ID, LONG_TEXT)
VALUES(X_media_id, p_note);
/* Insert into CSP_REPAIR_PO_HEADERS table the Requisition Header Id with the Item details */
INSERT INTO CSP_REPAIR_PO_HEADERS
(REPAIR_PO_HEADER_ID
,REQUISITION_HEADER_ID
,PURCHASE_ORDER_HEADER_ID
,INTERNAL_ORDER_HEADER_ID
,WIP_ID
,STATUS
,INVENTORY_ITEM_ID
,QUANTITY
,DEST_ORGANIZATION_ID
,NEED_BY_DATE
,REQUISITION_NUMBER
,REQUISITION_LINE_ID
,ORDER_LINE_ID
,LINE_NUM
,POREQ_LINE_RESERVATION_ID
,POREQ_LINE_ID
,ERROR_MESSAGE
,REPAIR_PROGRAM
,PO_NUMBER
,REPAIR_SUPPLIER_ID
,REPAIR_SUPPLIER_ORG_ID
,RECEIVED_QTY
--,SCRAP_QTY
--,ADJUSTED_QTY
)
VALUES
(
x_repair_po_header_id,
x_requisition_header_id , -- (+ 1 is stored in PO_REQUISITIONS_INTERFACE_ALL)
NULL,
NULL,
NULL,
'1',
l_line_rec.inventory_item_id,
l_line_rec.ordered_quantity,
l_header_rec.dest_organization_id,
l_header_rec.need_by_date,
x_requisition_number, --( REQ_NUMBER_SEGMENT1 from PO_REQUISITIONS_INTERFACE_ALL)
x_requisition_line_id,
x_order_line_id, ---- NULL
x_line_num, ---- NULL
x_poreq_line_reservation_id,---- NULL
x_repair_po_line_id, ---- NULL
NULL,
P_repair_program,
NULL,--PO_NUMBER
P_repair_supplier_id, --repair_supplier_id
P_repair_supplier_org_id, --Use "L_repair_supplier_org_id" if only P_repair_supplier_id is passed
NULL--received_qty
--NULL,--scrap_qty
--NULL--adjusted_qty
);
/* Insert into CSP_REPAIR_PO_LINES table all the reservation made for defective parts */
FOR I IN 1..l_out_reservation_tbl.COUNT
LOOP
l_out_reservation_rec := l_out_reservation_tbl(I);
select csp_repair_po_lines_s1.nextval
into l_repair_po_line_id
from dual;
INSERT INTO CSP_REPAIR_PO_LINES
(
REPAIR_PO_LINE_ID
,REPAIR_PO_HEADER_ID
,DEFECTIVE_ORGANIZATION_ID
,INVENTORY_ITEM_ID
,QUANTITY
,RESERVATION_ID
)
VALUES
(
l_repair_po_line_id,
x_repair_po_header_id,
l_out_reservation_rec.organization_id, ------- Defective Warehouse
l_out_reservation_rec.item_id, -------- Defective Item
l_out_reservation_rec.quantity_needed, -------- Defective Item Qty
l_out_reservation_rec.reservation_id -------- Reservation Id for the Defective Item
--P_need_by_date -------- Same as the need by date of Repair to item
);
Select haou.name
from hr_all_organization_units haou
where haou.organization_id = p_dest_organization_id;
Select MSIK.concatenated_segments item_number,
MSIK.description item_description,
MSIK.primary_uom_code
From mtl_system_items_kfv MSIK
Where MSIK.organization_id = P_organization_id
and MSIK.inventory_item_id = P_inventory_item_id
and sysdate between nvl(MSIK.start_date_active,sysdate)
and nvl(MSIK.end_date_active,sysdate);
SELECT a.oracle_username
INTO lv_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = p_apps_id;