____________________________________________________________________PART1__________________________________________________________________________________

CREATE TABLE JE_HEADER(
ID                			NUMBER,        
ENTRY_CREATOR              VARCHAR2(100), 
UNIQUE_IDENTIFIER          VARCHAR2(100), 
DOCUMENT_TYPE              VARCHAR2(100), 
POSTING_DATE               VARCHAR2(10),  
CURRENCY_CODE              VARCHAR2(100), 
CREATE_DATE                VARCHAR2(10),  
PROCESS_STATUS             VARCHAR2(10),  
PUBLISH_DATE               DATE,          
IZ_BUSINESS_ID             VARCHAR2(100))
/
ALTER TABLE JE_HEADER ADD CONSTRAINT PK_JEHEADER PRIMARY KEY (ID)
/
INSERT INTO JE_HEADER VALUES(20,'USFSS Sales','S201801201936202033','ZR','20180120','USD',to_char(current_date, 'YYYYMMDD'),null,null,null)
/
CREATE TABLE JE_DETAILS(
ID						NUMBER,
DIVISION_CODE           VARCHAR2(100), 
TOUCHPOINT_CODE			VARCHAR2(100),
LINE_NO					VARCHAR2(100),
LINE_DESCRIPTION		VARCHAR2(100),
LINE_CODE				VARCHAR2(100),
QUANTITY				VARCHAR2(100),
PRODUCT_CODE			VARCHAR2(100),
UNIT_OF_MEASURE			VARCHAR2(100),
TAXBASE_AMOUNT			VARCHAR2(100),
CREDIT_CARD_TYPE		VARCHAR2(100),
AMOUNT_VALUE			VARCHAR2(100), 
TAXCODE					VARCHAR2(100)) 
/
ALTER TABLE JE_DETAILS ADD CONSTRAINT fk_jedetails FOREIGN KEY (id) REFERENCES JE_HEADER(id)
/
SET DEFINE OFF

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', 1, 'Sales', NULL, '4', '015PS33100', 'EA', '312', NULL, '-312', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', '050PN36100', 'EA', '360', NULL, '-360', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', '050PP24100', 'EA', '360', NULL, '-360', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '6', '050PS33100', 'EA', '1080', NULL, '-1080', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', '050PS33200', 'EA', '288', NULL, '-288', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '4', '050PT29100', 'EA', '720', NULL, '-720', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '050PT40100', 'EA', '300', NULL, '-300', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PF27100', 'EA', '265', NULL, '-265', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PP24100', 'EA', '265', NULL, '-265', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '4', '100PS33100', 'EA', '1060', NULL, '-1060', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PS33200', 'EA', '212', NULL, '-212', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PT29100', 'EA', '265', NULL, '-265', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PT40100', 'EA', '460', NULL, '-460', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'BODLB22100', 'EA', '68', NULL, '-68', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'BODLJ17100', 'EA', '68', NULL, '-68', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'BODLR31100', 'EA', '68', NULL, '-68', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'CNDCC17100', 'EA', '75', NULL, '-75', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'CNDCC26100', 'EA', '170', NULL, '-170', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'CNDCF15100', 'EA', '150', NULL, '-150', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'CNDCP12100', 'EA', '150', NULL, '-150', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '8', 'CNDCS26100', 'EA', '600', NULL, '-600', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'CNDVF15100', 'EA', '65', NULL, '-65', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '8', 'DS05000500', 'EA', '240', NULL, '-240', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'DS16100500', 'EA', '285', NULL, '-285', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G000000500', 'EA', '16', NULL, '-16', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G002010100', 'EA', '0', NULL, '0', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G003010100', 'EA', '0', NULL, '0', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G004006100', 'EA', '0', NULL, '0', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'G004060100', 'EA', '76', NULL, '-76', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G005006100', 'EA', '16', NULL, '-16', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G006060100', 'EA', '30', NULL, '-30', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBB22100', 'EA', '90', NULL, '-90', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBJ17100', 'EA', '90', NULL, '-90', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBS33100', 'EA', '90', NULL, '-90', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBT29100', 'EA', '90', NULL, '-90', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P001500100', 'EA', '38', NULL, '-38', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P003250100', 'EA', '35', NULL, '-35', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P004150100', 'EA', '45', NULL, '-45', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '3', 'P005250100', 'EA', '81', NULL, '-81', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P006075100', 'EA', '11', NULL, '-11', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P006250100', 'EA', '54', NULL, '-54', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P008250100', 'EA', '40', NULL, '-40', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P010250100', 'EA', '24', NULL, '-24', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P013015100', 'EA', '28', NULL, '-28', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P015075100', 'EA', '50', NULL, '-50', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '4', 'P016075100', 'EA', '40', NULL, '-40', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P016250100', 'EA', '48', NULL, '-48', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMA09100', 'EA', '12', NULL, '-12', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '9', 'PSAMA13100', 'EA', '54', NULL, '-54', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '3', 'PSAMB22100', 'EA', '18', NULL, '-18', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAMF27100', 'EA', '6', NULL, '-6', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMJ17100', 'EA', '12', NULL, '-12', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAML18100', 'EA', '6', NULL, '-6', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAML41100', 'EA', '6', NULL, '-6', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAML41400', 'EA', '0', NULL, '0', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMN36100', 'EA', '12', NULL, '-12', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAMO27100', 'EA', '6', NULL, '-6', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMP24100', 'EA', '12', NULL, '-12', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAMR31100', 'EA', '6', NULL, '-6', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '15', 'PSAMS33100', 'EA', '90', NULL, '-90', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '11', 'PSAMT29100', 'EA', '66', NULL, '-66', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMV46100', 'EA', '12', NULL, '-12', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGA13100', 'EA', '52', NULL, '-52', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGR31100', 'EA', '52', NULL, '-52', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGS33100', 'EA', '52', NULL, '-52', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGT29100', 'EA', '52', NULL, '-52', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SOAPS33100', 'EA', '48', NULL, '-48', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'TRTC000100', 'EA', '308', NULL, '-308', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'TRTUR31200', 'EA', '102.67', NULL, '-102.67', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '3', 'TRTUT29200', 'EA', '154', NULL, '-154', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'TRTUY49200', 'EA', '51.33', NULL, '-51.33', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'VAT', NULL, NULL, NULL, 'EA', NULL, NULL, '-785.24', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'CASH', '550.17', '');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'AMERICANEXPRESS', '3035.49', '');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'VISA', '4824', '');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'DEBIT_VISA', '84.92', '');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'MASTERCARD', '649.63', '');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'DEBIT_MASTERCARD', '587.93', '');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', NULL, '1', 'PSAMA13100', 'EA', NULL, NULL, '2.1', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', NULL, '1', 'PSAMB22100', 'EA', NULL, NULL, '2.1', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', NULL, '1', 'PSAMO27100', 'EA', NULL, NULL, '2.1', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', 'Discovery Set 100ml', '1', '100PP24100', 'EA', NULL, NULL, '265', '62');

INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE) 
VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', 'Discovery Set 100ml', '1', '100PF27100', 'EA', NULL, NULL, '265', '62');

commit


____________________________________________________________________PART2__________________________________________________________________________________


create or replace FUNCTION CLOB_To_BLOB (p_CLOB IN CLOB) RETURN BLOB
AS
v_BLOB		BLOB;
v_RAW		RAW(32767);
v_Start		PLS_INTEGER := 1;
v_Buffer	PLS_INTEGER := 32767;
v_CLOB_Len	NUMBER;
v_Lob_Locator	BLOB := EMPTY_BLOB();
v_Loop_Ceil	NUMBER(10,4);
xmllength                   long;
BEGIN
	--If the Length of the CLOB is 0( NULL Clob then return)    
    xmllength := length(p_CLOB);
    dbms_output.put_line('p_CLOB length = '||xmllength);    
	v_CLOB_Len := DBMS_LOB.GETLENGTH(p_CLOB);
    
    dbms_output.put_line('Package p_CLOB length = '||v_CLOB_Len);
    
	IF NVL(v_CLOB_len,0) = 0
	THEN
		DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE,DBMS_LOB.SESSION);
		v_Lob_Locator := v_BLOB ;
		RETURN	v_Lob_Locator;
	END IF;
	----------------------------------------------------------------------------------
	--If the Length of the CLOB less thab 32K then set the Buffer Length to CLOB Lenth
	IF v_CLOB_Len < 32767
	THEN
		v_Buffer := v_CLOB_len;
	ELSE
		v_Buffer := 32767;
	END IF;
    
    dbms_output.put_line('Package v_Buffer1 length = '||v_Buffer);
	----------------------------------------------------------------------------------
	--DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);
	v_Loop_Ceil:=v_CLOB_Len/v_Buffer;    
    dbms_output.put_line('Package v_Loop_Ceil length = '||v_Loop_Ceil);

	FOR i IN 1..CEIL(v_Loop_Ceil)
	LOOP
    dbms_output.put_line('inside loop1');
		v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));
        dbms_output.put_line('inside loop2');
		DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);
        dbms_output.put_line('inside loop3');
		v_Start := v_Start + v_Buffer;
        dbms_output.put_line('inside loop4');
		v_Buffer := v_clob_len - v_Start + 1 ;
        dbms_output.put_line('Package v_Buffer2 length = '||v_Buffer);
    		IF (v_Buffer > 32767)
            THEN
            dbms_output.put_line('Package v_Buffer3 length = '||v_Buffer);
			v_Buffer := 32767;            
        	END IF;
	END LOOP;

	v_Lob_Locator := v_BLOB;
	DBMS_LOB.FREETEMPORARY(v_BLOB);
	RETURN v_Lob_Locator;
END CLOB_To_BLOB;


____________________________________________________________________PART3__________________________________________________________________________________


CREATE OR REPLACE PROCEDURE je_publish
AS 
      v_xml_clob              	CLOB;
      v_xml_blob                BLOB; 
BEGIN
        
 select 
         to_clob('<?xml version="1.0" encoding="UTF-8"?>') ||chr(10)||
        xmlserialize(document  
         xmlelement("JournalEntry"  
         , xmlelement("Header", xmlattributes('change' as "Action")  
		 , xmlelement("EntryCreator", jh.Entry_Creator) 
         , xmlelement("UniqueIdentifier", jh.Unique_Identifier) 
         , xmlelement("DocumentType", jh.Document_Type) 
         , xmlelement("PostingDate", jh.Posting_Date) 
         , xmlelement("CurrencyCode", jh.Currency_Code) 
         , xmlelement("CreateDate", jh.Create_Date) 
         )
         , xmlelement("Details"  
          --, XMLAttributes(count(*) as "rec_count")
           , xmlagg(  
               xmlelement("JournalDetail"  
                , xmlelement("DivisionCode", jd.Division_Code)
                , xmlelement("TouchPointCode", jd.TouchPoint_Code)
                , xmlelement("LineNo", jd.Line_No)
                , xmlelement("LineDescription", jd.Line_Description)
                , xmlelement("LineCode", jd.Line_Code)
                , xmlelement("Quantity", jd.Quantity)
                , xmlelement("ProductCode", jd.Product_Code)
                , xmlelement("UnitofMeasure", jd.Unit_of_Measure)
                , xmlelement("TaxBaseAmount", jd.TaxBase_Amount)
                , xmlelement("CreditCardType", jd.Credit_Card_Type)
                , xmlelement("AmountValue", jd.Amount_Value)
                , xmlelement("TaxCode", jd.TaxCode)
                )
                order by jd.Line_No
             )  
           )             
           )           
            indent --< for display purpose  
           )           
          as JournalEntry  
              into v_xml_clob
  from je_header jh  
       join je_details jd on jh.id = jd.id
  group by  jh.entry_creator
            , jh.Unique_Identifier
            , jh.Document_Type
            , jh.Posting_Date
            , jh.Currency_Code
            , jh.Create_Date;
		
            v_xml_blob := CLOB_TO_BLOB(v_xml_clob);           

END JE_publish;