sample

colnamefile.txt

help_category_id,
vivek1,
url\
vivek3,
datenow,
nullvalue,

Material.txt

source_id,
source_region,
source,
region,
env,
subenv,
plantdesc,
matnr,
txtlg,
ivend,
iven2,
iityp,
matkl,
matkldesc,
stprs,
iums,
iumcn,
ilots,
iioq,
ilead,
idraw,
igtec,
ifci,
imdcrt,
purchasehistory,
podate1,
podate,
total_std_cost,
material_cost,
freight_cost,
duty_cost,
other_cost,
category,
active,
ems_material_number,
globaluom,
sector_flag,
sourcing_type,
matnr_org,
franchise,
jnj_rm_code,

PO.txt

SOURCE_ID,
SOURCE_REGION,
SOURCE,
REGION,
ENV,
WERKS, 
MATNR,
MATNR_ORG,
LIFNR,
EBELN,
EBELP,
EBELC,
PO_CREATION_DATE,
REQUESTED_DATE,
NETPR ,
mucofa ,
PEINH,
PQORD,
PUM,
PECST,
MEINS,
MENGE,
DMBTR,
WAERS,
WAERSB,
PWHSE,
PTMKY,
PIEXRT,
PACST,
inco,
inco_desc,
SOURCING_TYPE, 
PO_type,
jnj_rm_code,
RUN_DATE,

PO_VS_SSE_RECONCILE.txt

SOURCE_ID, 
	SOURCE_REGION , 
	SOURCE,
	REGION,
	PO_PLANT,
	SSE_PLANT,
	SAPPLANTCODE,
	PLANTDESC,
	PO_NUMBER,
	PO_LINE_NUMBER,
	VENDOR_NUMBER,
	VENDOR_NAME,
	PO_CREATION_DATE,
	PO_REQ_DATE,
	COMPANY_CODE,
	PO_MATERIAL_NUMBER,
	SSE_MATERIAL_NUMBER,
	PO_MATERIAL_DESC,
	PO_CURRENCY,
	PLANT_CURRENCY,
	SSE_CURRENCY,
	PO_EXCHANGERATE,
	PO_ORDER_QTY ,
	SSE_MOQ ,
	PO_UOM,
	SSE_UOM,
	PRICE_VARIANCE_PERCENTAGE ,
	PO_INCO_TERM,
	INCO_TERM_DESC,
	SSE_INCOTERM,
	PO_LEAD_TIME,
	SSE_LEADTIME,
	SSE_CONTRACT_FOUND,
	PO_MATERIAL_GROUP,
	MATERIAL_GROUP_DESC,
	MATCH_CATEGORY,
	PO_ITEM_TYPE,
	COMPLIANT_INDICATOR,
	SSE_MATCH_FOUND,
	CURRENCY_MISMATCH_IND,
	LEAD_TIME_MISMATCH_IND,
	MOQ_MISMATCH_IND,
	UNIT_MISMATCH_IND,
	INCOTERM_MISMATCH_IND,
	VENDOR_MISMATCH_IND,
	OTHERSMISMATCH_IND,
	FAVOURABLE,
	GSS_SPEC_NUMBER,
	GSS_STATUS,
	MATERIAL_SPEC_REF,
	SSE_SPEC_REF, 
	PO_EXP_COST ,
	po_exp_cost_euro ,
	po_exp_cost_usd ,
	SSE_PRICE,
	sse_price_euro ,
	sse_price_usd ,
	TOTAL_NETVALUE_VARIANCE ,
	total_netvalue_variance_euro ,
	total_netvalue_variance_usd ,
	PO_TOTAL_NETVALUE ,
	PO_TOTAL_NETVALUE_euro ,
	PO_TOTAL_NETVALUE_usd ,
	PRICE_VARIANCE ,
	price_variance_euro ,
	price_variance_usd ,
	category,
	SUBCATEGORY,
	DIRECTION,
	REQ_YEAR,
	REQ_MONTH,
	SSE_START_DATE,
	SSE_END_DATE,
	req_date_int_format,
	EMS_MATERIAL_NUMBER,
	TIMELINE,
	BPVOLUME ,
	SOURCING_TYPE,

PPA_CONFIGURATION_TABLE

 sr_no,
 key,
 value,
 description,
 remarks,
 val1,
 val2,
 val3,
 val4,
 val5,

PPV_ReconcileMaterial.txt

SOURCE_ID,
SOURCE_REGION,
SOURCE,
REGION,
ENV,
subenv,
PLANTDESC,
MATNR,		
TXTLG,
IVEND,
IVEN2,
IITYP,
MATKL,
MATKLDESC,
STPRS,
IUMS,
IUMCN,
ILOTS,
IIOQ,
ILEAD,
IDRAW,
IGTEC,
IFCI,
IMDCRT,
PURCHASEHISTORY,
PODATE1,
PODATE,
TOTAL_STD_COST,
MATERIAL_COST,
FREIGHT_COST DOUBLE,
DUTY_COST DOUBLE,
OTHER_COST DOUBLE,
category,
ACTIVE,
EMS_MATERIAL_NUMBER,
GLOBALUOM,	
sector_flag,
SOURCING_TYPE, 
MATNR_ORG,
franchise,
jnj_rm_code,

PVC_OUTPUT.txt

RECTYPE ,
PLANT ,
MATERIAL ,
PERIOD ,
PONUMBER ,
PO_POLINE ,
PO_VENDORNUMBER ,
PO_PRICE ,
PO_QUANTITY ,
PO_CURRENCY ,
PO_CURRENTUOM ,
PO_UOMCF ,
PO_BASEUOM ,
PO_POTYPE ,
GR_GRNUMBER ,
GR_PONUMBER ,
GR_MATERIALNUMBER ,
GR_QUANTITY ,
GR_GRDATE ,
GR_PERIOD ,
GR_STATUS ,
GR_CALCMESSAGE ,
GR_LINKEDTOIR ,
G_DC_PPV_PPVTYPE ,
G_DC_PPV_CURRENCY ,
G_DC_PPV_INPUTQTY ,
G_DC_PPV_POPRICE ,
G_DC_PPV_QTPRICE ,
G_DC_PPV_MATCOST ,
G_DC_PPV_IRPRICE ,
G_DC_PPV_PPV ,
G_DC_PO_EX_TYPE ,
G_DC_PO_EX_EFFRATE ,
G_DC_QT_EX_TYPE ,
G_DC_QT_EX_EFFRATE ,
G_DC_MC_EX_TYPE ,
G_DC_MC_EX_EFFRATE ,
G_DC_IR_EX_TYPE ,
G_DC_IR_EX_EFFRATE ,
G_P_WHR_PPV_PPVTYPE ,
G_P_WHR_PPV_CURRENCY ,
G_P_WHR_PPV_INPUTQTY ,
G_P_WHR_PPV_POPRICE ,
G_P_WHR_PPV_QTPRICE ,
G_P_WHR_PPV_MATCOST ,
G_P_WHR_PPV_IRPRICE ,
G_P_WHR_PPV_PPV ,
G_P_WHR_PO_EX_TYPE ,
G_P_WHR_PO_EX_EFFRATE ,
G_P_WHR_QT_EX_TYPE ,
G_P_WHR_QT_EX_EFFRATE ,
G_P_WHR_MC_EX_TYPE ,
G_P_WHR_MC_EX_EFFRATE ,
G_P_WHR_IR_EX_TYPE ,
G_P_WHR_IR_EX_EFFRATE ,
G_P_BP_PPV_PPVTYPE ,
G_P_BP_PPV_CURRENCY ,
G_P_BP_PPV_INPUTQTY ,
G_P_BP_PPV_POPRICE ,
G_P_BP_PPV_QTPRICE ,
G_P_BP_PPV_MATCOST ,
G_P_BP_PPV_IRPRICE ,
G_P_BP_PPV_PPV ,
G_P_BP_PO_EX_TYPE ,
G_P_BP_PO_EX_EFFRATE ,
G_P_BP_QT_EX_TYPE ,
G_P_BP_QT_EX_EFFRATE ,
G_P_BP_MC_EX_TYPE ,
G_P_BP_MC_EX_EFFRATE ,
G_P_BP_IR_EX_TYPE ,
G_P_BP_IR_EX_EFFRATE ,
G_P_ACT_PPV_PPVTYPE ,
G_P_ACT_PPV_CURRENCY ,
G_P_ACT_PPV_INPUTQTY ,
G_P_ACT_PPV_POPRICE ,
G_P_ACT_PPV_QTPRICE ,
G_P_ACT_PPV_MATCOST ,
G_P_ACT_PPV_IRPRICE ,
G_P_ACT_PPV_PPV ,
G_P_ACT_PO_EX_TYPE ,
G_P_ACT_PO_EX_EFFRATE ,
G_P_ACT_QT_EX_TYPE ,
G_P_ACT_QT_EX_EFFRATE ,
G_P_ACT_MC_EX_TYPE ,
G_P_ACT_MC_EX_EFFRATE ,
G_P_ACT_IR_EX_TYPE ,
G_P_ACT_IR_EX_EFFRATE ,
QT_MATERIALNUMBER ,
QT_VENDORNUMBER ,
QT_STARTDATE ,
QT_ENDDATE ,
QT_PRICE ,
QT_MOQ ,
QT_CURRENTUOM ,
QT_UOMCF ,
QT_BASEUOM ,
SSE_MATERIALNUMBER ,
SSE_VENDORNUMBER ,
SSE_STARTDATE ,
SSE_ENDDATE ,
SSE_PRICE ,
SSE_MOQ ,
SSE_CURRENTUOM ,
SSE_UOMCF ,
SSE_BASEUOM ,
G_EP_DC_PPV_PPVTYPE ,
G_EP_DC_PPV_CURRENCY ,
G_EP_DC_PPV_INPUTQTY ,
G_EP_DC_PPV_POPRICE ,
G_EP_DC_PPV_QTPRICE ,
G_EP_DC_PPV_MATCOST ,
G_EP_DC_PPV_IRPRICE ,
G_EP_DC_PPV_PPV ,
G_EP_DC_PO_EX_TYPE ,
G_EP_DC_PO_EX_EFFRATE ,
G_EP_DC_QT_EX_TYPE ,
G_EP_DC_QT_EX_EFFRATE ,
G_EP_DC_MC_EX_TYPE ,
G_EP_DC_MC_EX_EFFRATE ,
G_EP_DC_IR_EX_TYPE ,
G_EP_DC_IR_EX_EFFRATE ,
G_EP_P_WHR_PPV_PPVTYPE ,
G_EP_P_WHR_PPV_CURRENCY ,
G_EP_P_WHR_PPV_INPUTQTY ,
G_EP_P_WHR_PPV_POPRICE ,
G_EP_P_WHR_PPV_QTPRICE ,
G_EP_P_WHR_PPV_MATCOST ,
G_EP_P_WHR_PPV_IRPRICE ,
G_EP_P_WHR_PPV_PPV ,
G_EP_P_WHR_PO_EX_TYPE ,
G_EP_P_WHR_PO_EX_EFFRATE ,
G_EP_P_WHR_QT_EX_TYPE ,
G_EP_P_WHR_QT_EX_EFFRATE ,
G_EP_P_WHR_MC_EX_TYPE ,
G_EP_P_WHR_MC_EX_EFFRATE ,
G_EP_P_WHR_IR_EX_TYPE ,
G_EP_P_WHR_IR_EX_EFFRATE ,
G_EP_P_BP_PPV_PPVTYPE ,
G_EP_P_BP_PPV_CURRENCY ,
G_EP_P_BP_PPV_INPUTQTY ,
G_EP_P_BP_PPV_POPRICE ,
G_EP_P_BP_PPV_QTPRICE ,
G_EP_P_BP_PPV_MATCOST ,
G_EP_P_BP_PPV_IRPRICE ,
G_EP_P_BP_PPV_PPV ,
G_EP_P_BP_PO_EX_TYPE ,
G_EP_P_BP_PO_EX_EFFRATE ,
G_EP_P_BP_QT_EX_TYPE ,
G_EP_P_BP_QT_EX_EFFRATE ,
G_EP_P_BP_MC_EX_TYPE ,
G_EP_P_BP_MC_EX_EFFRATE ,
G_EP_P_BP_IR_EX_TYPE ,
G_EP_P_BP_IR_EX_EFFRATE ,
G_EP_P_ACT_PPV_PPVTYPE ,
G_EP_P_ACT_PPV_CURRENCY ,
G_EP_P_ACT_PPV_INPUTQTY ,
G_EP_P_ACT_PPV_POPRICE ,
G_EP_P_ACT_PPV_QTPRICE ,
G_EP_P_ACT_PPV_MATCOST ,
G_EP_P_ACT_PPV_IRPRICE ,
G_EP_P_ACT_PPV_PPV ,
G_EP_P_ACT_PO_EX_TYPE ,
G_EP_P_ACT_PO_EX_EFFRATE ,
G_EP_P_ACT_QT_EX_TYPE ,
G_EP_P_ACT_QT_EX_EFFRATE ,
G_EP_P_ACT_MC_EX_TYPE ,
G_EP_P_ACT_MC_EX_EFFRATE ,
G_EP_P_ACT_IR_EX_TYPE ,
G_EP_P_ACT_IR_EX_EFFRATE ,
MC_YEAR,
MC_COST ,
MC_CURRENCY ,
MC_CURRENTUOM ,
MC_UOMCF ,
MC_BASEUOM ,
IR_IRNUMBER ,
IR_PONUMBER ,
IR_MATERIALNUMBER ,
IR_QUANTITY ,
IR_IRDATE ,
IR_PRICE,
IR_CURRENCY ,
IR_CURRENTUOM ,
IR_UOMCF ,
IR_BASEUOM ,
I_DC_PPV_PPVTYPE ,
I_DC_PPV_CURRENCY ,
I_DC_PPV_INPUTQTY ,
I_DC_PPV_POPRICE ,
I_DC_PPV_QTPRICE ,
I_DC_PPV_MATCOST ,
I_DC_PPV_IRPRICE ,
I_DC_PPV_PPV ,
I_DC_PO_EX_TYPE ,
I_DC_PO_EX_EFFRATE ,
I_DC_QT_EX_TYPE ,
I_DC_QT_EX_EFFRATE ,
I_DC_MC_EX_TYPE ,
I_DC_MC_EX_EFFRATE ,
I_DC_IR_EX_TYPE ,
I_DC_IR_EX_EFFRATE ,
I_P_WHR_PPV_PPVTYPE ,
I_P_WHR_PPV_CURRENCY ,
I_P_WHR_PPV_INPUTQTY ,
I_P_WHR_PPV_POPRICE ,
I_P_WHR_PPV_QTPRICE ,
I_P_WHR_PPV_MATCOST ,
I_P_WHR_PPV_IRPRICE ,
I_P_WHR_PPV_PPV ,
I_P_WHR_PO_EX_TYPE ,
I_P_WHR_PO_EX_EFFRATE ,
I_P_WHR_QT_EX_TYPE ,
I_P_WHR_QT_EX_EFFRATE ,
I_P_WHR_MC_EX_TYPE ,
I_P_WHR_MC_EX_EFFRATE ,
I_P_WHR_IR_EX_TYPE ,
I_P_WHR_IR_EX_EFFRATE ,
I_P_BP_PPV_PPVTYPE ,
I_P_BP_PPV_CURRENCY ,
I_P_BP_PPV_INPUTQTY ,
I_P_BP_PPV_POPRICE ,
I_P_BP_PPV_QTPRICE ,
I_P_BP_PPV_MATCOST ,
I_P_BP_PPV_IRPRICE ,
I_P_BP_PPV_PPV ,
I_P_BP_PO_EX_TYPE ,
I_P_BP_PO_EX_EFFRATE ,
I_P_BP_QT_EX_TYPE ,
I_P_BP_QT_EX_EFFRATE ,
I_P_BP_MC_EX_TYPE ,
I_P_BP_MC_EX_EFFRATE ,
I_P_BP_IR_EX_TYPE ,
I_P_BP_IR_EX_EFFRATE ,
I_P_ACT_PPV_PPVTYPE ,
I_P_ACT_PPV_CURRENCY ,
I_P_ACT_PPV_INPUTQTY ,
I_P_ACT_PPV_POPRICE ,
I_P_ACT_PPV_QTPRICE ,
I_P_ACT_PPV_MATCOST ,
I_P_ACT_PPV_IRPRICE ,
I_P_ACT_PPV_PPV ,
I_P_ACT_PO_EX_TYPE ,
I_P_ACT_PO_EX_EFFRATE ,
I_P_ACT_QT_EX_TYPE ,
I_P_ACT_QT_EX_EFFRATE ,
I_P_ACT_MC_EX_TYPE ,
I_P_ACT_MC_EX_EFFRATE ,
I_P_ACT_IR_EX_TYPE ,
I_P_ACT_IR_EX_EFFRATE ,
IR_PERIOD,
IR_STATUS ,
IR_CALCMESSAGE ,
IR_CURRENCY1 ,
IR_PRICE1,

selectexprfile.txt

help_category_id as vivek,
(parent_category_id * parent_category_id) as vivek1,
url as   \
vivek3,
substr(now(),1,4) as datenow,
case when(url is not null) then 'not null' else 'null value' END as nullvalue,

selectexprfile_ppvreconcile.txt

1,
	PPV_source_id as source_id,
    PPV_source_region as source_region,
	SUBSTRING(cast(period as string),1,4) AS POSTING_YEAR,
	regexp_replace(SUBSTRING(cast(period as string),5,2), "^0+", '') AS POSTING_MONTH,
	CASE WHEN ir_irdate is not null and ir_irdate <> "" THEN regexp_replace(ir_irdate, "-", "")  \
	ELSE regexp_replace(gr_grdate, "-", "") END as INVOICE_DATE,
	
	(plant) AS ENV,
	ir_irnumber,
	ponumber,
	po_poline as EBELP,
	material AS MATERIAL_CODE,
    TXTLG AS MATERIAL_DESC,
	
	CASE \
		WHEN ACTIVE = 'No' \
			THEN 'Others' \
		ELSE ppv_category \
		END AS category,

	po_currentuom AS PURCHASING_UOM,
	po_baseuom AS STOCKING_UOM,
	cast(1/po_uomcf as string)  AS CONVERSION_FACTOR,
	ir_quantity AS INV_QUANTITY,
	gr_quantity AS RECEIPT_QUANTITY,
	po_currency AS PURCHASE_CURRENCY,
	
	CASE WHEN g_p_act_po_ex_effrate is not null then g_p_act_po_ex_effrate else i_p_act_po_ex_effrate end as er_ACTUALRATE,
	CASE WHEN g_p_whr_po_ex_effrate is not null then g_p_whr_po_ex_effrate else i_p_whr_po_ex_effrate end as er_WAHR,
	CASE WHEN g_p_bp_po_ex_effrate is not null then g_p_bp_po_ex_effrate else i_p_bp_po_ex_effrate end as er_BP,
	cast(po_price as decimal(30,10)) AS POUNITPRICE_DOC_CURRENCY,
	case \
	when g_p_act_po_ex_effrate is not null \
	     then po_price * g_p_act_po_ex_effrate \
	     else po_price * i_p_act_po_ex_effrate  \
	end AS POUNITPRICE_FCUR_ACTUALRATE,
	
    case \
	when g_p_whr_po_ex_effrate is not null \ 
	     then po_price * g_p_whr_po_ex_effrate \
	     else  po_price * i_p_whr_po_ex_effrate \
	end AS POUNITPRICE_FCUR_WAHR,
	
	case \
	when g_p_bp_po_ex_effrate is not null \
	     then po_price * g_p_bp_po_ex_effrate \
         else po_price * i_p_bp_po_ex_effrate	\
	END AS POUNITPRICE_FCUR_BP,
	po_price * po_quantity AS TOTALPO_AMT_DOC_CURRENCY,
	case \
	when g_p_act_po_ex_effrate is not null \
	     then po_price * po_quantity * g_p_act_po_ex_effrate \
	     else po_price * po_quantity * i_p_act_po_ex_effrate \
    end as TOTALPO_AMT_FCUR_ACTUALRATE,
	case \
	when g_p_whr_po_ex_effrate is not null  \
	     then po_price * po_quantity * g_p_whr_po_ex_effrate \
         else po_price * po_quantity * i_p_whr_po_ex_effrate \
    end	AS TOTALPO_AMT_FCUR_WAHR,

selectsqlpo.txt

SELECT DISTINCT EBELN, MATNR, SOURCING_TYPE AS SOURCING_TYPE, PO_TYPE FROM PO_SOURCING

ssegroupbysql.txt

 SELECT sse_env as SOURCEPLANT,
       plantcode as SOURCEMATERIALID,
	   --award_start_date ,
	   --award_end_date,
       max(category) AS category,
       max(final_category) AS subcategory,
	   max(trade_name) as tradename,
	   max(spec) as spec
FROM sse
group  BY sse_env,
         plantcode

sseheader.txt

 country  ,
                                     sse_place  ,
									 sse_env  ,
									 jj_plant_location  ,
									 final_category  ,
									 spec  ,
									 plantcode  ,
									 material_group  ,
									 trade_name  ,
									 supplier_name  ,
                                     SSE_PRICE ,									 
									 award_start_date  ,
									 award_end_date  ,
									 currency  ,
									 incoterm  ,
									 min_order_qty  ,
									 leadtime  ,
									 contract_status  ,
									 vendorno  ,
									 vendorcity  ,
									 sse_uom  ,
									 category ,
									 PricingQtyPer ,
									 PREDECESSOR ,
									 BPVOLUME ,
									 Std_price ,
                                     market_movement 	,
                                     second_tier ,
                                     CIP	,
                                     jnj_driven ,
									 REGION ,
									 sse_price_in_base_uom ,
									 sse_base_uom ,
									 matnr_org ,
               						 run_date timestamp	,

STDCOST.txt

SOURCE_ID,
SOURCE_REGION,
SOURCE,
REGION,
ENV,
ENV2,
MATNR,
MATNR_ORG,
YEAR,
TOTAL_STD_COST,
MATERIAL_COST,
FREIGHT_COST,
DUTY_COST,
OTHER_COST,
EXTRACTDATE,

vendors.sql

SOURCE_ID,
SOURCE_REGION,
SOURCE,
REGION,
ENV,     
LIFNR,
KTOKK,
STRAS,
ORT01,
REGIO,
LAND1,
ZIPC,
CITY,
NAME2,
NAME3,
TELF1,
NAME1,
affiliate,
SAP_VENDORCODE,
PARENT_VENDOR_NO,