[Home] [Help]
20: TYPE RCPT_LINES_Rec_Type IS RECORD
21: (
22: rma_number oe_order_headers_all.order_number%type,
23: rma_header_id oe_order_headers_all.header_id%type ,
24: line_id oe_order_lines_all.line_id%type ,
25: rma_line_number oe_order_lines_all.line_number%type ,
26: inventory_item_id oe_order_lines_all.inventory_item_id%type,
27: org_name hr_all_organization_units.name%type ,
28: organization_id rcv_transactions.organization_id%type,
21: (
22: rma_number oe_order_headers_all.order_number%type,
23: rma_header_id oe_order_headers_all.header_id%type ,
24: line_id oe_order_lines_all.line_id%type ,
25: rma_line_number oe_order_lines_all.line_number%type ,
26: inventory_item_id oe_order_lines_all.inventory_item_id%type,
27: org_name hr_all_organization_units.name%type ,
28: organization_id rcv_transactions.organization_id%type,
29: unit_of_measure rcv_transactions.unit_of_measure%type,
22: rma_number oe_order_headers_all.order_number%type,
23: rma_header_id oe_order_headers_all.header_id%type ,
24: line_id oe_order_lines_all.line_id%type ,
25: rma_line_number oe_order_lines_all.line_number%type ,
26: inventory_item_id oe_order_lines_all.inventory_item_id%type,
27: org_name hr_all_organization_units.name%type ,
28: organization_id rcv_transactions.organization_id%type,
29: unit_of_measure rcv_transactions.unit_of_measure%type,
30: received_quantity rcv_transactions.quantity%type ,
63: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
64: inventory_item_id csd_repairs.inventory_item_id%type,
65: ro_uom csd_repairs.unit_of_measure%type,
66: requisition_number po_requisition_headers_all.segment1%type,
67: ordered_quantity oe_order_lines_all.ordered_quantity%type,
68: order_number oe_order_headers_all.order_number%type
69: );
70:
71: TYPE SHIP_LINES_Rec_Type IS RECORD
83: order_number wsh_delivery_details.source_header_number%type ,
84: sales_order_header wsh_delivery_details.source_header_id%type ,
85: locator_id wsh_delivery_details.locator_id%type,
86: released_status wsh_delivery_details.released_status%type, --bug#14657082 --bug#15859195
87: order_line_number oe_order_lines_all.line_number%type ,
88: date_shipped oe_order_lines_all.actual_shipment_date%type ,
89: line_id oe_order_lines_all.line_id%type , --Bug#6779806
90: repair_number csd_repairs.repair_number%type,
91: repair_line_id csd_repairs.repair_line_id%type,
84: sales_order_header wsh_delivery_details.source_header_id%type ,
85: locator_id wsh_delivery_details.locator_id%type,
86: released_status wsh_delivery_details.released_status%type, --bug#14657082 --bug#15859195
87: order_line_number oe_order_lines_all.line_number%type ,
88: date_shipped oe_order_lines_all.actual_shipment_date%type ,
89: line_id oe_order_lines_all.line_id%type , --Bug#6779806
90: repair_number csd_repairs.repair_number%type,
91: repair_line_id csd_repairs.repair_line_id%type,
92: ro_uom csd_repairs.unit_of_measure%type ,
85: locator_id wsh_delivery_details.locator_id%type,
86: released_status wsh_delivery_details.released_status%type, --bug#14657082 --bug#15859195
87: order_line_number oe_order_lines_all.line_number%type ,
88: date_shipped oe_order_lines_all.actual_shipment_date%type ,
89: line_id oe_order_lines_all.line_id%type , --Bug#6779806
90: repair_number csd_repairs.repair_number%type,
91: repair_line_id csd_repairs.repair_line_id%type,
92: ro_uom csd_repairs.unit_of_measure%type ,
93: ro_item_id csd_repairs.inventory_item_id%type ,
100: org_name hr_all_organization_units.name%type
101: );
102: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
103: (
104: header_id oe_order_lines_all.header_id%type,
105: line_id oe_order_lines_all.line_id%type,
106: ordered_quantity oe_order_lines_all.ordered_quantity%type,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
101: );
102: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
103: (
104: header_id oe_order_lines_all.header_id%type,
105: line_id oe_order_lines_all.line_id%type,
106: ordered_quantity oe_order_lines_all.ordered_quantity%type,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
109: req_number oe_order_lines_all.orig_sys_document_ref%type ,
102: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
103: (
104: header_id oe_order_lines_all.header_id%type,
105: line_id oe_order_lines_all.line_id%type,
106: ordered_quantity oe_order_lines_all.ordered_quantity%type,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
109: req_number oe_order_lines_all.orig_sys_document_ref%type ,
110: inventory_item_id oe_order_lines_all.inventory_item_id%type,
103: (
104: header_id oe_order_lines_all.header_id%type,
105: line_id oe_order_lines_all.line_id%type,
106: ordered_quantity oe_order_lines_all.ordered_quantity%type,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
109: req_number oe_order_lines_all.orig_sys_document_ref%type ,
110: inventory_item_id oe_order_lines_all.inventory_item_id%type,
111: shipment_date oe_order_lines_all.actual_shipment_date%type ,
104: header_id oe_order_lines_all.header_id%type,
105: line_id oe_order_lines_all.line_id%type,
106: ordered_quantity oe_order_lines_all.ordered_quantity%type,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
109: req_number oe_order_lines_all.orig_sys_document_ref%type ,
110: inventory_item_id oe_order_lines_all.inventory_item_id%type,
111: shipment_date oe_order_lines_all.actual_shipment_date%type ,
112: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
105: line_id oe_order_lines_all.line_id%type,
106: ordered_quantity oe_order_lines_all.ordered_quantity%type,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
109: req_number oe_order_lines_all.orig_sys_document_ref%type ,
110: inventory_item_id oe_order_lines_all.inventory_item_id%type,
111: shipment_date oe_order_lines_all.actual_shipment_date%type ,
112: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
113: shipped_quantity wsh_delivery_details.shipped_quantity%type,
106: ordered_quantity oe_order_lines_all.ordered_quantity%type,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
109: req_number oe_order_lines_all.orig_sys_document_ref%type ,
110: inventory_item_id oe_order_lines_all.inventory_item_id%type,
111: shipment_date oe_order_lines_all.actual_shipment_date%type ,
112: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
113: shipped_quantity wsh_delivery_details.shipped_quantity%type,
114: del_line_serial_num wsh_serial_numbers.fm_serial_number%type ,
107: req_header_id oe_order_lines_all.source_document_id%type ,
108: req_line_id oe_order_lines_all.source_document_line_id%type ,
109: req_number oe_order_lines_all.orig_sys_document_ref%type ,
110: inventory_item_id oe_order_lines_all.inventory_item_id%type,
111: shipment_date oe_order_lines_all.actual_shipment_date%type ,
112: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
113: shipped_quantity wsh_delivery_details.shipped_quantity%type,
114: del_line_serial_num wsh_serial_numbers.fm_serial_number%type ,
115: lot_number wsh_delivery_details .lot_number%type,
2205: cs_estimate_details ced,
2206: csd_repairs cra,
2207: rcv_transactions rcvt,
2208: oe_order_headers_all oeh,
2209: oe_order_lines_all oel,
2210: hr_all_organization_units haou
2211: WHERE cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
2212: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2213: AND ced.order_header_id is not null
2233: AND paramn1 = rcvt.transaction_id)
2234:
2235: AND ((ced.QUANTITY_REQUIRED < -1
2236: AND oel.line_id in ( Select line_id
2237: from oe_order_lines_all oel1
2238: start with oel1.line_id = ced.order_line_id
2239: connect by prior oel1.line_id = oel1.split_from_line_id
2240: and oel1.shipped_quantity is not null
2241: and oel1.header_id = oeh.header_id))
2283: cs_estimate_details ced,
2284: csd_repairs cra,
2285: rcv_transactions rcvt,
2286: oe_order_headers_all oeh,
2287: oe_order_lines_all oel,
2288: hr_all_organization_units haou
2289: WHERE cra.creation_date between p_from_date and p_to_date
2290: AND cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
2291: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2312: AND paramn1 = rcvt.transaction_id)
2313:
2314: AND ((ced.QUANTITY_REQUIRED < -1
2315: AND oel.line_id in ( Select line_id
2316: from oe_order_lines_all oel1
2317: start with oel1.line_id = ced.order_line_id
2318: connect by prior oel1.line_id = oel1.split_from_line_id
2319: and oel1.shipped_quantity is not null
2320: and oel1.header_id = oeh.header_id))
2354: ced.inventory_item_id prod_txn_item_id
2355: FROM hr_all_organization_units haou,
2356: csd_repairs cra,
2357: oe_order_headers_all oeh,
2358: oe_order_lines_all oel,
2359: rcv_transactions rcvt,
2360: cs_estimate_details ced,
2361: csd_product_transactions cpt
2362: WHERE cpt.repair_line_id = p_repair_line_id
2386:
2387:
2388: AND ((ced.QUANTITY_REQUIRED < -1
2389: AND oel.line_id in ( Select line_id
2390: from oe_order_lines_all oel1
2391: start with oel1.line_id = ced.order_line_id
2392: connect by prior oel1.line_id = oel1.split_from_line_id
2393: and oel1.shipped_quantity is not null
2394: and oel1.header_id = oeh.header_id))
3303: oeh.order_number
3304: from csd_product_transactions cpt,
3305: csd_repairs cra,
3306: po_requisition_headers_all prh,
3307: oe_order_lines_all oel,
3308: oe_order_headers_all oeh
3309: where cpt.repair_line_id = cra.repair_line_id
3310: AND cpt.req_header_id = prh.requisition_header_id
3311: AND cpt.order_line_id = oel.line_id
3339: oeh.order_number
3340: from csd_product_transactions cpt,
3341: csd_repairs cra,
3342: po_requisition_headers_all prh,
3343: oe_order_lines_all oel,
3344: oe_order_headers_all oeh
3345: where cpt.repair_line_id = cra.repair_line_id
3346: AND cpt.req_header_id = prh.requisition_header_id
3347: AND cpt.order_line_id = oel.line_id
4717: oeh.order_number
4718: from csd_product_transactions cpt,
4719: csd_repairs cra,
4720: po_requisition_headers_all prh,
4721: oe_order_lines_all oel,
4722: oe_order_headers_all oeh
4723: where cpt.repair_line_id = cra.repair_line_id
4724: AND cpt.req_header_id = prh.requisition_header_id
4725: AND cpt.order_line_id = oel.line_id
4753: oeh.order_number
4754: from csd_product_transactions cpt,
4755: csd_repairs cra,
4756: po_requisition_headers_all prh,
4757: oe_order_lines_all oel,
4758: oe_order_headers_all oeh
4759: where cpt.repair_line_id = cra.repair_line_id
4760: AND cpt.req_header_id = prh.requisition_header_id
4761: AND cpt.order_line_id = oel.line_id
6386: wsh_serial_numbers dsn,--Added to fix 3801614
6387: --Changed to view from table, bug: 4341784
6388: wsh_delivery_assignments_v wda,
6389: wsh_new_deliveries wnd,
6390: oe_order_lines_all oel,
6391: hr_all_organization_units hao
6392: Where cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
6393: AND cpt.estimate_detail_id = ced.estimate_detail_id
6394: AND dd.delivery_detail_id = wda.delivery_detail_id
6411: where crh.repair_line_id = cpt.repair_line_id
6412: and crh.paramn1 = dd.delivery_detail_id
6413: and event_code = 'PS')
6414: AND oel.line_id in ( Select line_id
6415: from oe_order_lines_all oel1
6416: start with oel1.line_id = ced.order_line_id
6417: connect by prior oel1.line_id = oel1.split_from_line_id
6418: and oel1.shipped_quantity is not null
6419: and oel1.header_id = oel.header_id);
6463: wsh_serial_numbers dsn,--Added to fix 3801614
6464: --Changed to view from table, bug: 4341784
6465: wsh_delivery_assignments_v wda,
6466: wsh_new_deliveries wnd,
6467: oe_order_lines_all oel,
6468: hr_all_organization_units hao
6469: Where cra.creation_date between p_from_date and p_to_date
6470: AND cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
6471: AND cpt.estimate_detail_id = ced.estimate_detail_id
6489: where crh.repair_line_id = cpt.repair_line_id
6490: and crh.paramn1 = dd.delivery_detail_id
6491: and event_code = 'PS')
6492: AND oel.line_id in ( Select line_id
6493: from oe_order_lines_all oel1
6494: start with oel1.line_id = ced.order_line_id
6495: connect by prior oel1.line_id = oel1.split_from_line_id
6496: and oel1.shipped_quantity is not null
6497: and oel1.header_id = oel.header_id);
6548: wsh_serial_numbers dsn,--Added to fix 3801614
6549: --Changed to view from table, bug: 4341784
6550: wsh_delivery_assignments_v wda,
6551: wsh_new_deliveries wnd,
6552: oe_order_lines_all oel,
6553: hr_all_organization_units hao
6554: Where cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
6555: AND cpt.estimate_detail_id = ced.estimate_detail_id
6556: AND dd.delivery_detail_id = wda.delivery_detail_id
6570: and crh.paramn1 = dd.delivery_detail_id
6571: and event_code = 'PS')
6572: AND cpt.repair_line_id = p_repair_line_id
6573: AND oel.line_id in ( Select line_id
6574: from oe_order_lines_all oel1
6575: start with oel1.line_id = ced.order_line_id
6576: connect by prior oel1.line_id = oel1.split_from_line_id
6577: and oel1.shipped_quantity is not null
6578: and oel1.header_id = oel.header_id);
7324: prh.segment1 requisition_number,
7325: hao.name source_org_name,
7326: hao1.name destination_org_name,
7327: trl.txn_source_id
7328: from oe_order_lines_all oel,
7329: wsh_delivery_details dd,
7330: wsh_serial_numbers dsn,--Added to fix 3801614
7331: po_requisition_lines_all prl,
7332: po_requisition_headers_all prh,
7381: prh.segment1 requisition_number,
7382: hao.name source_org_name,
7383: hao1.name destination_org_name,
7384: trl.txn_source_id
7385: from oe_order_lines_all oel,
7386: wsh_delivery_details dd,
7387: wsh_serial_numbers dsn,--Added to fix 3801614
7388: po_requisition_lines_all prl,
7389: po_requisition_headers_all prh,
9707:
9708:
9709: CURSOR c_product_transaction_id(p_repair_line_id IN number) is
9710: SELECT c.product_transaction_id, a.booked_flag
9711: FROM oe_order_lines_all a,
9712: cs_estimate_details b,
9713: csd_product_transactions c
9714: WHERE a.line_id = b.order_line_id
9715: AND b.estimate_detail_id = c.estimate_detail_id
9768:
9769: CURSOR CANCELLED_ORDER_LINES(p_repair_line_id NUMBER) IS
9770: SELECT DISTINCT cpt.product_transaction_id PRODUCT_TXN_ID
9771: FROM oe_order_headers_all oeh,
9772: oe_order_lines_all oel,
9773: cs_estimate_details ced,
9774: csd_product_transactions cpt
9775: WHERE cpt.repair_line_id = p_repair_line_id
9776: AND cpt.action_type in ('RMA', 'SHIP', 'RMA_THIRD_PTY', 'SHIP_THIRD_PTY')
9789: -- will show as cancelled.
9790: /***
9791: AND ((ced.QUANTITY_REQUIRED < -1
9792: AND oel.line_id in ( Select line_id
9793: from oe_order_lines_all oel1
9794: start with oel1.line_id = ced.order_line_id
9795: connect by prior oel1.line_id = oel1.split_from_line_id
9796: and oel1.shipped_quantity is not null
9797: and oel1.header_id = oeh.header_id))