[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Select the bucket pattern from chv_bucket_patterns based on
** incoming parameter bucket_pattern_id including the week
** start day.
** We have to hardcode the date due to nls issue when using the next_day function. We have to use
** a known date to get the correct 3 char day in the current language.
*/
SELECT decode(cbp.week_start_day,'1_MONDAY' , to_char(to_date('31/01/2000','DD/MM/YYYY'),'DY'),
'2_TUESDAY' , to_char(to_date('01/02/2000','DD/MM/YYYY'),'DY'),
'3_WEDNESDAY' , to_char(to_date('02/02/2000','DD/MM/YYYY'),'DY'),
'4_THURSDAY' , to_char(to_date('03/02/2000','DD/MM/YYYY'),'DY'),
'5_FRIDAY' , to_char(to_date('04/02/2000','DD/MM/YYYY'),'DY'),
'6_SATURDAY' , to_char(to_date('05/02/2000','DD/MM/YYYY'),'DY'),
'7_SUNDAY' , to_char(to_date('06/02/2000','DD/MM/YYYY'),'DY'),
to_char(to_date('31/01/2000','DD/MM/YYYY'),'DY')
),
cbp.number_daily_buckets,
cbp.number_weekly_buckets,
cbp.number_monthly_buckets,
cbp.number_quarterly_buckets
INTO x_week_start_day,
x_day_count,
x_week_count,
x_month_count,
x_quarter_count
FROM chv_bucket_patterns cbp
WHERE cbp.bucket_pattern_id = p_bucket_pattern_id;
** This has to be done because the insert_buckets program will fail
** since the PL/SQL tables are transposed into a record and a hard code
** insert is done.
*/
for i in x_bucket_count..60 loop
x_bucket_descriptor_table(x_bucket_count) := null ;
po_message_s.sql_error('Insert_Row', X_progress, sqlcode);
p_row_select_order IN NUMBER,
p_row_type IN VARCHAR2,
p_bucket_table IN BKTTABLE) IS
x_progress varchar2(3) := NULL ;
x_last_updated_by NUMBER ;
x_last_updated_by := fnd_global.user_id ;
/* Insert the record type into the table */
insert into chv_horizontal_schedules
(SCHEDULE_ID,
SCHEDULE_ITEM_ID,
ROW_SELECT_ORDER,
ROW_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
COLUMN5,
COLUMN6,
COLUMN7,
COLUMN8,
COLUMN9,
COLUMN10,
COLUMN11,
COLUMN12,
COLUMN13,
COLUMN14,
COLUMN15,
COLUMN16,
COLUMN17,
COLUMN18,
COLUMN19,
COLUMN20,
COLUMN21,
COLUMN22,
COLUMN23,
COLUMN24,
COLUMN25,
COLUMN26,
COLUMN27,
COLUMN28,
COLUMN29,
COLUMN30,
COLUMN31,
COLUMN32,
COLUMN33,
COLUMN34,
COLUMN35,
COLUMN36,
COLUMN37,
COLUMN38,
COLUMN39,
COLUMN40,
COLUMN41,
COLUMN42,
COLUMN43,
COLUMN44,
COLUMN45,
COLUMN46,
COLUMN47,
COLUMN48,
COLUMN49,
COLUMN50,
COLUMN51,
COLUMN52,
COLUMN53,
COLUMN54,
COLUMN55,
COLUMN56,
COLUMN57,
COLUMN58,
COLUMN59,
COLUMN60,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
VALUES(p_schedule_id,
p_schedule_item_id,
p_row_select_order,
p_row_type,
sysdate,
x_last_updated_by,
sysdate,
x_last_updated_by,
p_bucket_table(1),
p_bucket_table(2),
p_bucket_table(3),
p_bucket_table(4),
p_bucket_table(5),
p_bucket_table(6),
p_bucket_table(7),
p_bucket_table(8),
p_bucket_table(9),
p_bucket_table(10),
p_bucket_table(11),
p_bucket_table(12),
p_bucket_table(13),
p_bucket_table(14),
p_bucket_table(15),
p_bucket_table(16),
p_bucket_table(17),
p_bucket_table(18),
p_bucket_table(19),
p_bucket_table(20),
p_bucket_table(21),
p_bucket_table(22),
p_bucket_table(23),
p_bucket_table(24),
p_bucket_table(25),
p_bucket_table(26),
p_bucket_table(27),
p_bucket_table(28),
p_bucket_table(29),
p_bucket_table(30),
p_bucket_table(31),
p_bucket_table(32),
p_bucket_table(33),
p_bucket_table(34),
p_bucket_table(35),
p_bucket_table(36),
p_bucket_table(37),
p_bucket_table(38),
p_bucket_table(39),
p_bucket_table(40),
p_bucket_table(41),
p_bucket_table(42),
p_bucket_table(43),
p_bucket_table(44),
p_bucket_table(45),
p_bucket_table(46),
p_bucket_table(47),
p_bucket_table(48),
p_bucket_table(49),
p_bucket_table(50),
p_bucket_table(51),
p_bucket_table(52),
p_bucket_table(53),
p_bucket_table(54),
p_bucket_table(55),
p_bucket_table(56),
p_bucket_table(57),
p_bucket_table(58),
p_bucket_table(59),
p_bucket_table(60),
x_login_id,
null,
null,
null,
null ) ;
po_message_s.sql_error('Insert_Row', X_progress, sqlcode);
x_row_select_order number ;
/* Calculate Past Due Bucket Quantity by selecting
** total ORDER_QUANTITY from CHV_ITEM_ORDERS
** based on the due_date.
*/
begin
x_progress := '020' ;
select nvl(sum(round(cio.order_quantity,5)),0),
nvl(sum(round(cio.order_quantity_primary,5)),0)
into p_past_due_qty,
p_past_due_qty_primary
from chv_item_orders cio
where cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and cio.supply_document_type = 'RELEASE'
and trunc(cio.due_date) <= to_date(p_bucket_end_date_table(x_bucket_count),'YYYY/MM/DD') ;
insert into po_session_gt
(num1,
num2 )
select nvl(sum(round(cio.order_quantity,5)),0) release_quantity,
0 forecast_quantity
from chv_item_orders cio
where cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and cio.supply_document_type = 'RELEASE'
and trunc(cio.due_date) <= to_date(p_bucket_end_date_table(x_bucket_count),'YYYY/MM/DD')
AND p_bucket_descriptor_table(x_bucket_count) = 'PAST_DUE'
UNION ALL
SELECT Nvl(SUM(release_qty),0) release_quantity,Nvl(SUM(forecast_qty),0) forecast_quantity
FROM
(SELECT DECODE (
cio.supply_document_type,
'RELEASE',
DECODE (p_schedule_type,
'FORECAST_ALL_DOCUMENTS', 0,
'FORECAST_ONLY', 0,
NVL (ROUND (cio.order_quantity, 5), 0))
) release_qty,
DECODE (
cio.supply_document_type,
'RELEASE',
DECODE (p_schedule_type,
'FORECAST_ALL_DOCUMENTS',
NVL (ROUND (cio.order_quantity, 5), 0),
0),
DECODE (p_schedule_type,
'RELEASE_ONLY',0 ,
'FORECAST_ALL_DOCUMENTS', 0,
NVL (ROUND (cio.order_quantity, 5), 0))
)forecast_qty
FROM chv_item_orders cio
WHERE cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and cio.supply_document_type IN ('RELEASE', 'PLANNED_ORDER', 'REQUISITION')
AND p_schedule_type IN ('FORECAST_ALL_DOCUMENTS','FORECAST_ONLY','MATERIAL_RELEASE', 'RELEASE_WITH_FORECAST','RELEASE_ONLY')
and trunc(cio.due_date) between to_date(p_bucket_start_date_table(x_bucket_count),'YYYY/MM/DD')
AND to_date(p_bucket_end_date_table(x_bucket_count),'YYYY/MM/DD')
and p_bucket_descriptor_table(x_bucket_count) in ('DAY','BUFFER','WEEK','MONTH','QUARTER') )
union all
SELECT
nvl(sum(round(cio.order_quantity,5)),0) release_quantity ,
0 forecast_quantity
from chv_item_orders cio
where cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and cio.supply_document_type = 'RELEASE'
and trunc(cio.due_date) > to_date(p_bucket_start_date_table(x_bucket_count),'YYYY/MM/DD')
and p_bucket_descriptor_table(x_bucket_count) = 'FUTURE' ;
update po_session_gt
set num3 = Nvl(num1,0)+ Nvl(num2,0);
update po_session_gt psg1
SET num4 = (SELECT
SUM(SUM(num1)) OVER
(ORDER BY rowid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+
SUM(SUM(num2)) OVER
(ORDER BY rowid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+
nvl(p_cum_quantity_received,0)
FROM po_session_gt psg2
where psg1.rowid= psg2.rowid
GROUP BY psg2.rowid
);
select num1,/*release+_quantity_table*/
num2,/*forecast_quantity_table*/
num3/*total_quantity_table*/,
num4/*cum_quantity_table*/
bulk collect into x_release_quantity_table
,x_forecast_quantity_table
,x_total_quantity_table
,x_cum_quantity_table
from po_session_gt;
DELETE FROM po_session_gt;
/* Initialize row_select_order and row_type and call stored
** procedure insert_buckets by passing the appropriate PL/SQL table
** to insert the record into CHV_HORIZONTAL_SCHEDULES
*/
x_row_select_order := 1 ;
x_row_select_order ,
x_row_type ,
p_bucket_descriptor_table ) ;
x_row_select_order := 2 ;
x_row_select_order ,
x_row_type ,
p_bucket_start_date_table ) ;
x_row_select_order := 3 ;
x_row_select_order ,
x_row_type ,
x_release_quantity_table ) ;
x_row_select_order := 4 ;
x_row_select_order ,
x_row_type ,
x_forecast_quantity_table ) ;
x_row_select_order := 5 ;
x_row_select_order ,
x_row_type ,
x_total_quantity_table ) ;
x_row_select_order := 6 ;
x_row_select_order ,
x_row_type ,
x_cum_quantity_table ) ;
x_row_select_order := 7 ;
x_row_select_order ,
x_row_type ,
p_bucket_end_date_table ) ;